Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys. This is a follow up from my previous question. I'm using the following expression to display the amount of certain items in a pivot tabe over a given period of time.
SUM(AGGR(FirstSortedValue(Amount ,-(Date - (Amount/1E10))), Item, Company))
Now for a single year, I get correct values. But if drill down on quarter, month or day obviously I can't expect a correct value because I didn't define the time period in my Aggr() expression.
If I add Date to the aggregation I get the reversed result, meaning I can see individual amounts for every day but then they are all summed up when I return to an annual overiew.
What I like to see however is the amount of each item for the last day based on the selected time period. So for example in 2019 the pivot should return the amount of each item based on max date, which are then summed up by dimension 1. Further drilldowns should follow the same principle resulting in sums based on the max date of the selected drilldown.
See pictures attached for better understanding.
Thank you for you help.
Pivot1: Year Overview
Pivot2: Year expanded