Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I have a staright table with 'month year' and sum(sales)
For each monthyear i want to know the corresponding last year quarter sum of sales.I want to add a row beneath which specifies this total.for example underneath may 2011 the value will be sum(sales) for apr 2010+may2010+jun 2010,underneath jun 2011 it will be same and changes for july since its different quarter.Any ideas plz.
Thank you.My final expression was
Rangesum(Before([Sales],10+Mod(Month(MonthYear)-1, 3),3))
this works for every monthyear
You can calculate accumulation with step 3 with expression:
=rangesum(before(sum(sales)), sum(sales), after(sum(sales)))
Then you can add a condition to show it only for those moths:
=if(Mod(Month(MonthYear)-1, 3)=1, rangesum(before(sum(sales)), sum(sales), after(sum(sales))), null())
Thank you.My final expression was
Rangesum(Before([Sales],10+Mod(Month(MonthYear)-1, 3),3))
this works for every monthyear
Both solutions has a small drawback.
To show correct number for a quarter all months from the quarter shoud present in the table.
I have added zero values for each and every combination of master link table in event link and calendar link to over come the problem of some months missing