How to show rolling 13 periods for a variety of field selections

[#1]

Hello Experts.

I have been trying to figure out a calculation in qlikview that is somewhat complex in Excel using SUMIF or SUMPRODUCT. I am calculating the Average Unit Volume for the Rolling 13 Periods. So far I have only been successful with a calculation for one period at a time, but I'd like to display the rolling 13P for the last 13 periods in a table manner. I could create 13 boxes and have each of them calculate AUV for each period, but I was hoping there is an easier way of doing it in a table.

This is a nested aggregation that works that calculates the AUV for Rolling 13P for current period only or for only one given period.

sum(AGGR(SUM({<[TimeOrder]={'>=$(=$(eCurrentTimeOrder)-12) <=$(=$(eCurrentTimeOrder))'}>} Value)/

Count({<[TimeOrder]={'>=$(=$(eCurrentTimeOrder)-12) <=$(=$(eCurrentTimeOrder))'},Value={'>0'}>} Value),

[Period]))

where

eCurrentTimeOrder = MAX({1<[Period]={'$(eCurrentPeriod)'}>} [TimeOrder])  (had to create a support table that would attach sequential numeric values to my fiscal periods)

and

eCurrentPeriod = is most recent period

I have tried using pivot and straight tables, but both are limiting to the number of periods that is displayed. For example accumulate doesn't accumulate beyond the periods shown in a table.

I am attaching a sample document which has been greatly reduced to a sample population. (I've got a lot more periods and a lot more entities).

Any help would be appreciated.

Original question posted here back in October http://community.qlik.com/thread/95092

[whiteline]

Hi.

Have you seen this doc http://community.qlik.com/docs/DOC-4252 by Gysbert Wassenaar ?

[#3]

if you provide period in months  and years  then its easy task for Rolling months

[#4]

hi Elena

please Findattachment

hope this helps

[#5]

The following posts may help you.

Re: How to Create chart with Rolling values

Re: Rolling 12 months sum

Regards,

Sajeevan

[#6]

Set Analysis are computed ONCE per chart, not once per row or cell: it is normal that the computation is made on only one period.

You will need to change the data model so that you link one period (in selection) to 13 of the fact table. That is the concept of AsOfTable.

Gysbert's document is a very good one explaining the steps and why you need to do. See link above in whiteline 's answer. I had published also a document to explain that topic: http://community.qlik.com/docs/DOC-4821

Fabrice

[#7]

hi ElenaKhegay,

have u seen my last Solution which i have provided

[#8]

Thank you @qlik learn. The solution you have provided does not seem to calculate the rolling sum of previous 13 periods. What it does is just shows the periodic average - which is correct - but not quite what I am looking for.

[#9]

Thanks - yes,  I have been referred to this very helpful document many times. Does the AsOfTable work on non-time dimension...like P1, P2, etc. instead of months and years? I tried it and it doesn't seem to pick it up.

[#10]

You can create a rolling period even with your type of data. Please find attached the modified QV file. You need to refer the rolling period generation script to understand how this is being done.

Hope this solution helps you.

Regards,

Sajeevan