Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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