lastValue in pivot table uses wrong value

Q&A
,
1

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.

image
image742×452 11.5 KB

2

Hi @m.zakelj ,

Welcome to the Quick Sight 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

3

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

1 Like
4

Hello @m.zakelj, I am curious, is my updated version of the Arena analysis showing the expected value?

lastValue in pivot table uses wrong value

I set the total calculation for the lastValue field to Max (since this returns the same value on every row). Then I set the total aggregation for the difference to average. That seems to be returning the values I would expect from the table.

I’ll mark this as the solution, but please let me know if you have any further questions. Thank you!

5

That is exactly, what i was looking for, thanks!

1 Like