Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, guys!
Is where any options how to calculate previous value using Above() function, if the previous value isn't shown in a pivot table?
For example, if we want to display the amount of sales for the current day and compare it with the amount for the previous day, we'll get the fallowing result:
sum(sales) | above(sum(sales)) | |
01.04.2013 | 10 | - |
02.04.2013 | 15 | 10 |
But how to show the previous value for 01.04.2013?
The above function can only reference values in rows (or columns) in the chart. For what you want you'll have to precalculate the values in the script. Something like:
T1:
Load date, sales
from ...mysource...;
T2:
Load date, sum_sales, previous(sum_sales) as prev_sum_sales;
Load date, sum(sales) as sum_sales)
resident T1
group by date;
Thank you Gysbert! I have thought about previous function.. But I need to show at the same pivot table values for the current day, previous day, the same day at the previous month. So, it would be perfect to use above(sales, 1), above(sales, 7), above (sales, 30).
I could use join in the script, but the problem is that i have 25 dimensions. And the combination of these 25 dim is unique almost all the time.
You could try using a variation on the AsOf table. 'Self-join' the table on the dates -1, -7 and -30 days earlier. So every date is linked to three previous dates at the lowest level of granularity. You can then sum in the charts over any combination of dimensions you like.