Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help with aggr function

hi,

     I have a staright table with 'month year'sales.png 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.

1 Solution

Accepted Solutions
Not applicable
Author

Thank you.My final expression was

Rangesum(Before([Sales],10+Mod(Month(MonthYear)-1, 3),3))

this works for every monthyear

View solution in original post

4 Replies
whiteline
Master II
Master II

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())

Not applicable
Author

Thank you.My final expression was

Rangesum(Before([Sales],10+Mod(Month(MonthYear)-1, 3),3))

this works for every monthyear

whiteline
Master II
Master II

Both solutions has a small drawback.

To show correct number for a quarter all months from the quarter shoud present in the table.

Not applicable
Author

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