Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Jack - Have you got chance to work on this? I am curious to know the outcome...
Cheers!
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
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