topic Rolling Twelve Months in QlikView

Rolling Twelve Months

pkelly — Tue, 09 Feb 2010 13:30:55 GMT

I have been asked by a user to write a report which shows....

End Jan 2010 - Total of Previous 12 Month Sales to End Jan 2010

End Dec 2009 - Total of Previous 12 Month Sales to End Dec 2009

End Nov 2009 - Total Previous 12 Mmonths Sales to End Nov 2009

Going back to March 2008 (our QVD extract takes into account April 2007)

Sort of like 12 months rolling sales but showing what the figures are for each historical month...

I have two tables...

Calendar and Sales Invoice

These are joing by a field called %AnalysisPeriodKey which eqates to the financial year / period.

200901 = April 2009, 200902 = May 2009 and so on...

In my calendar table, I have a sequence field which 1 = April 2007, 2 = May 2007 and so on...

Would appreciate some guidance on how I can achieve this...

Rolling Twelve Months

Tue, 09 Feb 2010 14:53:26 GMT

Hi Paul

There are many posts offering solutions for Rolling Months, most of them using set analysis. Once you understand the format for set analysis, you can set your PeriodKey to determine the rolling period.

Jason Long created a very good demo called YTD calculations using Set Analysis which gives you a good idea on how to use set analysis to determine date limits. The url is http://community.qlik.com/media/p/82604.aspx. An example of set analysis could be:

SUM( $ <PeriodKey <= {$(=MAX(PeriodKey)) >= $(=MAX(PeriodKey)-12 }> Sales)

Again, try searching for "Rolling months" or "Set analysis" in this forum and you could be pleasantly surprised.

Rolling Twelve Months

pkelly — Tue, 09 Feb 2010 14:57:44 GMT

Thanks Jeanne

Set Analysis isn't an option for me just now as I am currently still on version 8.2 (working towards upgrading at the end of next month)..

Suppose what I am really looking for is a Moving Average Total (12 months back)...

How do I achieve this without set analysis

Rolling Twelve Months

johnw — Wed, 10 Feb 2010 01:42:09 GMT

The simple answer is properties -> expressions-> accumulation -> accumulate 12 steps back. For a moving average, just divide by 12.

I'm guessing that your situation is more complicated than that?

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 09:36:17 GMT

Thanks John

I am doing this in a pivot table and the accumlation options are greyed out...

The attached spreadsheet shows what I am trying to achieve.

Please bear in mind that I am on version 8.2....

Rolling Twelve Months

Wed, 10 Feb 2010 09:54:11 GMT

Try this one. Hope this helps

=RangeAvg(Above(Sum(sales),0,4))

Thanks

-Raghu.

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 10:39:02 GMT

Thanks Raghu

Have tried your suggestion but all I am getting is a repeat of the sales per month...

i.e.

Jan Feb

Sales =(sum(sin_Sales) 100 110

MAT =(RangeAvg(Above(Sum(sin_Sales),0,4)) 100 110

Rolling Twelve Months

Wed, 10 Feb 2010 11:38:35 GMT

My formula give the Average of 4 months, If you want 12 months summation

(RangeSum(Above(Sum(sin_Sales),0,12))

you want to achieve summation of all months of that year am i right?

-Raghu

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 12:46:09 GMT

Apologies Raghu, I haven't made my earlier response as clear as it should be...

As I said, I tried the formula which you suggested but it is not providing an average but repeating the data which is shown for the specific month...

Say I had the following...

Jan 2010 Sales £150

Dec 2009 Sales £100

Nov 2009 Sales £120

Oct 2009 Sales £110

Would exect the average to be... (150 + 100 + 120 + 110) / 4 = £120

What I am getting is...

Jan 2010 £150

Dec 2009 £100

Nov 2009 £120

Oct 2009 £110

i.e. the monthly sales line repeated...no averaging is taking place...

Hope this illustrates the problem better...

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 15:28:40 GMT

Some further info for anyone who can help...

The formula which Raghu provided almost works...

Issue I have is that it is giving me a running total on my month - I need a cumulative total and by family rather than by month...

Hope someone can help....

Additional Detail...

I am working on a pivot where I have three dimensions...

Family, Period and Month

Pivot is layed out with family to left and period / month at top...like a crosstab type situation...

Period........................

Month......................

Family (Summed Value)

I think that this may be the reason I am getting what I am getting with above...

Rather than going above I want to go right - is this possible - am I on the right track

Rolling Twelve Months

johnw — Wed, 10 Feb 2010 18:07:48 GMT


Paul Kelly wrote: I think that this may be the reason I am getting what I am getting with above...

Yes, that's why you're getting what you're getting with above(). Above() in a pivot table refers to the current "column segment". The column segment is all values of the last dimension in the context of a single value of the previous dimension, if I remember correctly.

In Excel at least, your Period field is the same as the month. It displays as 200910, while the month displays as Jan-10. Above can ONLY select months within the current period. The period is only a month. Therefore, the above() function can't return any more than the current cell, the current month.

Why is the period dimension there at all? It is more than a month in your actual chart?

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 19:59:16 GMT

Thanks for the response John...

Financial Year Period and MonthYear are there because some of our users prefer working with financial year period (200910) whilst some prefer to see the month year (January 2010) - trying to keep evryone happy...

I have playing around today trying to crack this with no success - the spreadsheet I attached ealier "QlikView Example.xls" (Feb 10 @ 8:36) shows what I am trying to achieve...

Set analysis may solve this for me but we will not be upgrading to version 9 for at least another 6 weeks...

Any assistance in cracking this would be grateful...

Rolling Twelve Months

johnw — Wed, 10 Feb 2010 20:05:37 GMT

How about creating a cyclic group with Financial Year Period and MonthYear in it, and use the cyclic group as the dimension? Then the users can select which one they want to use, and as a side effect, it should cure your rolling 12-month headache.

And no, set analysis won't crack it. A set is calculated once for the entire chart. So if you make a "last 12 month" set, every row/column in your chart will use the SAME 12 months. You could crack it with a month island, but I think that's the wrong solution.

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 22:03:13 GMT

Thanks John

I have mocked up the live report from work at home using QlikView 9 Personal Edition (I hope this can be opened by other licensed users)..

I have also attached a spreadsheet which shows the expected results for the MAT expression......

(this is in the post below - still haven't workied out how to add two files)

Would appreciate assistance on what the formula for this row should be ....

Rolling Twelve Months

pkelly — Wed, 10 Feb 2010 22:04:20 GMT

expected results

Rolling Twelve Months

johnw — Wed, 10 Feb 2010 22:51:38 GMT

Bah! I didn't think above(), below(), before() and after() were sensitive to the orientation of the pivot chart itself. I thought they were fixed based simply on the order of the dimensions. I was wrong.

So, in this case, your months are sorted in descending order, and they're laid out horizontally. Therefore, you need to use after() instead of above() to pick up the data in the columns that occur after this one. I suppose that's more intuitive, but it leads to data that changes if you start pivoting.

So I got the results with:

rangeavg(after(sum(sin_Sales),0,12))

Since it can compute an average even with less than 12 months, it does, so if you really want zero for the first eleven months, you'll have to play with it further.

Rolling Twelve Months

pkelly — Thu, 11 Feb 2010 09:17:26 GMT

Thanks John - you have made a young man very happy....

I have a calendar sequence field in my live report - can probably say if this is less than 12 do not calculate to prevent this appearing if below 12 months...

Thanks to everone else who helped....

Rolling Twelve Months

Thu, 04 Aug 2011 11:26:40 GMT

Hello John,

Just a note to thank you for your post/solution on this subject. I was beginning to think what I required was impossible but this pointed me in correct direction. I hadn't realised how powerfull the combination of range and inter-record functions could be.

Not the first time one of your posts has helped me out.

Regards, Paul.

Rolling Twelve Months

Wed, 18 Apr 2012 22:41:44 GMT

John:

I understand that this post is quite old, but is there a way to chart the rangeavg results (MAT in your test file) in a Bar Chart?

Thank you,

Braden

Re: Rolling Twelve Months

Anonymous — Wed, 16 May 2018 02:36:51 GMT

how to go if i want zero for the first eleven months