Rolling Twelve Months

[pkelly]

Hi

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...

[#2]

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.

[pkelly]

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

[johnw]

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?

[pkelly]

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....

[#6]

Try this one. Hope this helps

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

Thanks

-Raghu.

[pkelly]

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

[#8]

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

[pkelly]

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...

[pkelly]

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