4 Replies Latest reply: Jun 10, 2011 5:21 AM by rawdenja

# 12 Month Moving Average Set Analysis-Plotting onto a Graph

Hi there,

I'm relatively new to Qlikview and having a little trouble with a set analysis that I am working on.

What I am trying to do is a 12 month moving average. I have look at the forums and managed to create it in a table using the following set analysis:-

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

However when trying to apply this to a chart it stops working.

Any help is gratefully recieved.

Thanks,

Jack

• ###### 12 Month Moving Average Set Analysis-Plotting onto a Graph

Jack - It would be good if you can post QV document with some sample data. I am sure one of us will guide you. The idea is to build a robust data model to accept data month on month and code re-usability.

Just to give you some ideas... here are few points to consider.

1. Calculate the CurrentYear & PreviousYear by variables (Something like this...)

CurrentYear : Max({1} Year)

PreviousYear : Max({1} Year) - 1

2. Calculate CurrentMonth by variables (See below...)

CurrentMonth : Max({1<Year = {\$(=\$(vMaxYear))}>} MonthNumber)

3. Use the variables in Set Analysis

Sum({1<Year={\$(=(\$(vPreviousYear)))}, MonthNumber={\$(=(\$(vCurrentMonthNumber)))}>} Sales))

I hope this helps!

Cheers.

• ###### 12 Month Moving Average Set Analysis-Plotting onto a Graph

Hi DV,

I have had a look at this and afraid your example doesn't work. I'm using a data model designed by a colleague of mine and cannot work out a way to add the CurrentMonth variables to it (I can't see the code behind it).

Is there a way that this can run without the current month feature, for example using something like this?

This displays sales for the previous 12 months, however when trying to average the results is incorrect.

Thanks For your help, Sorry I oculdn't work it out with your previous example!

Regards,

Jack

• ###### 12 Month Moving Average Set Analysis-Plotting onto a Graph

Hi Everyone,

It appears the answer is on a different post, See link below.

http://community.qlik.com/message/56320#56320

hopefully this can help the 1000+ viewers of this post.

Jack

• ###### 12 Month Moving Average Set Analysis-Plotting onto a Graph

Jack - Have you got chance to work on this? I am curious to know the outcome...

Cheers!