When i use lastValue aggregation function in the pivot table, it actually calculates average, not the last value. I have 3 row dimensions: A, B and date.
i calculated the calc_margin as SUM(X) / SUM(Y) and last day margin as lastValue(calc_margin, [date ASC], [A, B]). While the values at the bottom level for last day are shown correctly, at the B level (row with 0, second level), it shows wrong value. It only shows correct value, when i set the value option ‘totals’ : ‘average’ instead of ‘totals’:'default, BUT, i need last_day_margin for further calculation and it uses the wrong value.
Hi @m.zakelj ,
Welcome to the QuickSight Community!
Can you kindly publish sample data in the arena and share with us to understand what is getting computed now against what is expected?
I understand the totals are not yielding as expected. But the partition and the aggregation will be the probable reason for this. Help us understand what are the variable A,B and X and Y?
Thanks,
Prantika
Hey, there’s a link to the arena analysis. Even here, the problem is present. I have two dimensions, sport and date, and margin as value. I want to capture the last day margin for each sport, then subtract the last day value from the average value, but instead of last day value, the average is used, so the difference on sport level is 0, but for day to day calculations, it works.
testing