Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Accumulation rolling MQT with multiple dimensions

Hi All,

I have a question concerning calculating rolling MQT with multiple dimensions. I have this table with company sales per MonthYear. The Sales column is raw sales. And the Accumulated column is what I want. It adds the sales from the current MonthYear with the sales from the previous 2 MonthYears for that Company (first 2 months should not accumulate, because at least 3 months are needed). How to calculate this properly with an expression in QV (currently I calculated it by hand)?

I checked this link: Calculating rolling n-period totals, averages or other aggregations, but it uses an example with 1 dimension only.

I have tried checking 'Accumulation' with 3 steps back, but this does not seem to work unless I first make a selection of 1 company.

I have tried different combinations of aggr(), rangesum(), above(), TOTAL, rowno(), etc. without success.

MonthYear Company Sales Accumulated
01/07/2014A100100
01/07/2014B4545
01/07/2014C3333
01/08/2014A120120
01/08/2014B5555
01/08/2014C5050
01/09/2014A150370
01/09/2014B100200
01/09/2014C80163
01/10/2014A130400
01/10/2014B95250
01/10/2014C75205

Hope some Qlik colleagues here have the golden tip!

Many thanks

7 Replies

Re: Accumulation rolling MQT with multiple dimensions

How about this?

Aggr(If(RowNo() >= 3, RangeSum(Above(Sum(Sales), 0, 3)), Sum(Sales)), Company, MonthYear)


Capture.PNG

Not applicable

Re: Accumulation rolling MQT with multiple dimensions

Hi Sunny,

Thanks for that input . It worked fine. Now I used the same trick for totals to get percentages.

Any idea for how to get rid of the MonthYear 01/07/2014 and 01/08/2014 in the graph to not confuse people that those are also MQT values?

Best,

Neal

Re: Accumulation rolling MQT with multiple dimensions

Would you able to share the expected output for percentages and how it should look once you remove MonthYear in table form (just like you did in your initial post)?

Not applicable

Re: Accumulation rolling MQT with multiple dimensions

So I already calculated percentages the way I need them. But now I want the 'Bolded' rows to dissapear because they only contain data for 1 or 2 months. While I need only the rows where 3 months are accumulated.

In dimension limitations tab there is an option to show first x values, but not last x values. Or enable conditional perhaps, or a calculated dimension. In the background the data should still be there, but only those MonthYears with at least 2 before them should be shown. Any ideas?

MonthYear Company Sales Sales Accumulated Sales Total MY Total Sales Acc Final %
1033 1033  
01/07/2014A10010017817856%
01/07/2014B454517817825%
01/07/2014C333317817819%
01/08/2014A12022022540355%
01/08/2014B5510022540325%
01/08/2014C508322540321%
01/09/2014A15037033073350%
01/09/2014B10020033073327%
01/09/2014C8016333073322%
01/10/2014A13040030085547%
01/10/2014B9525030085529%
01/10/2014C7520530085524%

Re: Accumulation rolling MQT with multiple dimensions

This?

Capture.PNG

Not applicable

Re: Accumulation rolling MQT with multiple dimensions

Hi Sunny,

Yes that was the solution I also found. By just using an if statement in the expression the first 2 months can be excluded from the graph. Thanks for your help!

Neal

Re: Accumulation rolling MQT with multiple dimensions

No problem Neal

Best,

Sunny