topic Re: help with aggr function in QlikView

help with aggr function

Tue, 04 Sep 2012 02:56:30 GMT

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.

Re: help with aggr function

whiteline — Tue, 04 Sep 2012 04:20:18 GMT

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

Re: help with aggr function

Tue, 04 Sep 2012 15:45:52 GMT

Thank you.My final expression was

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

this works for every monthyear

Re: help with aggr function

whiteline — Tue, 04 Sep 2012 15:58:59 GMT

Both solutions has a small drawback.

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

Re: help with aggr function

Sun, 10 Feb 2013 19:58:16 GMT

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