Treating lastValue as an Aggregation

[MKuhns01]

Hi,

The function LastValue states it returns the Last Value of an aggregated measure.

Is there any way this can be used AS an aggregated measure in a calculated field?

The use case is for values such as credit balances. Our company desires to trend these over time, with the ability to drill up to look at yearly changes, or drill down to see daily changes.

When plotting on a line chart, a method of aggregation needs to be chosen. We can’t choose Min, Max, Avg, Sum, etc because when balances are trended over time its usually assumed that the depicted number on the chart is the amount at the start or end of that day/week/month/year.

I used a method to change the Date level with a parameter + control, but our primary intention with Quicksight is for external use and this solution isn’t exactly user-friendly. If it were only internally, this work around would be okay.

If Quicksight was able to recognize lastValue as its own aggregation on the visuals, that would be perfect. It doesn’t appear that it does currently, I receive a “Table Calculation attribute reference(s) are missing in field wells” error.

Is there a way to trick Quicksight into accepting lastValue as an aggregated measure? Or is it potentially an update yet to come?

I appreciate any help, thanks!

[Max]

You can wrap it in an aggregation like this.

minOver(lastValue({cost},[{date} ASC]))

Does this help?

[MKuhns01]

I wish it did. It still errors out with the same message as above. I’ve tried with different types of LAC-A and LAC-W functions.

maxOver(lastValue({cost}, [date ASC])) - Same Error as above on Visual

max(lastValue({cost}, [date ASC])) - Mismatched Aggregation within Calculated Field edit screen

[Max]

You need to reference all of the fields in your visual.

To get around this I would suggest moving your logic into SQL so that you can report on these fields without having to reference all of them in your visual.

[sagmukhe]

Hello @MKuhns01 - Thank you for posting your query. I am trying to provide my idea below. It will definitely not be how you want it to be dynamic. But still sharing my thought process. Please check and let me know if this will solve your problem. You need to create calculated fields for Month/Quarter/Year Balances. I have just tried out Monthly Balances.

sumover(ifelse({Order Date} = maxover({Order Date}, [{Order Month}], PRE_AGG), Sales, 0), [{Order Month}], PRE_AGG)

See the below snapshot for some more reference:

Similarly other fields can be derived by modifying the calculations to derive Quarterly and Yearly Balances. Hope this helps!

[Koushik_Muthanna]

Here is another example of balance aggregation in democentral : https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Balance-Aggregation

[MKuhns01]

Hi @Koushik_Muthanna and @sagmukhe,

I appreciate the answers, along with Max’s about moving the logic to SQL.

Unfortunately, I have tried this solution in the past but ultimately it was deemed to not work for our end users, because we are opting to use the native drill down by time period on other visuals. Our end users would try the native drill down on our series using the methods you mentioned, and would end up ultimately misreporting the value on the visual when doing so.

I don’t expect moving the logic to SQL will work either, because the query would have to change upon use of the drill down, but when reviewing the queries sent to our DB, it doesn’t appear to change.

[MKuhns01]

I found a semi-solution I wanted to post in case anyone in the future runs into this issue.

What I opted to do was use RunningSum() to calculate the balance, and then use a date filter on a different date field as described in this Q&A :

How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed. Write a calc like this: minOver(min({order date}), [{order date}]) And then put your filter on that. Should work! Before: [image] After: [image]

I can filter dates and drill down without misrepresenting the value. I have to use a parameter + control to allow users to change the group by method, but this seems to be the best option with how things are currently.

I then use a calculated field to map the parameter + control to the fields in my dataset.

My formula for Balance looks like:

runningSum({charges}, [{date} ASC], [{Grouping Parameter Calc Field}]) - runningSum({payments}, [{date} ASC], [Grouping Parameter Calc Field}])

And then I have the Grouping Parameter Calc Field in the Group By field well of the Visualization.