topic 12 Month Moving Average Set Analysis-Plotting onto a Graph in QlikView

12 Month Moving Average Set Analysis-Plotting onto a Graph

Wed, 13 Apr 2011 13:42:18 GMT

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

IAMDV — Wed, 13 Apr 2011 13:53:53 GMT

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

IAMDV — Thu, 14 Apr 2011 09:48:51 GMT

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

Cheers!

12 Month Moving Average Set Analysis-Plotting onto a Graph

Thu, 14 Apr 2011 10:07:57 GMT

Hi DV,

Thanks for the reply, Apologies for not replying sooner.

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?

Sum(if(Date>addmonths(Date(today()-Day(Today())),-12),Sales).

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

Fri, 10 Jun 2011 09:21:19 GMT

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