Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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.

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: help with aggr function

Thank you.My final expression was

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

this works for every monthyear

4 Replies
whiteline
Honored Contributor II

Re: help with aggr function

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

Re: help with aggr function

Thank you.My final expression was

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

this works for every monthyear

whiteline
Honored Contributor II

Re: help with aggr function

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

Re: help with aggr function

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

Community Browser