Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling 12 Month Average

Right, battled hard over that last few days to figure this one out but I had no luck.  Hopefully someone here will be able to help:

the following set analysis is used to create the respective chart:

=Sum({$<[Milestone Num]={27},

  [Activity Measure Code]={'WATER'},

  [Activity Period Month Year]={">=$(vStartMonthYear)<=$(vEndMonthYear)"}>}

  [Activity Value])

Dimension=[Activity Period Year Month]

Capture_chart.JPG.jpg

As you can see the vStartMonthYear and vEndMonthYear are variables that let me garb the Year to date range.

The figures displayed in the chart are not what the users are after, instead of having a sum for the month, they want a sum for the 12 months leading to the month.

So, for example the value for:

Apr-2013 will be a sum of May-2012 to Apr-2013

May-2013 will be a sum of Jun-2012 to May-2013

Jun-2013 will be a sum of Jul-2012 to Jun-2013

and so on....

As well as the sum, they also want the average, which should be easy as it will always divide by 12.

I believe this is how a rolling 12 month average works, so i'm hoping someone has achieved this in QlikView.

I would like the table to be displayed as above with the same dimension.

is this possible with set analysis, if so, how??

5 Replies
MK_QSL
MVP
MVP

Not applicable
Author

Hi manishkachhia I have already seen this and used it to achieve an accumulation of the value over 12 months, so:

Jan = Jan

Feb = Jan+Feb

Mar = Jan+Feb+Mar

The article was good for this, however my challenge I believe is slightly different in that against a month (eg. Apr-2013) I want to go and get the sum for the 12 months previous  (eg. May-2012 to Apr-2013) and put this sum against the month of Apr-2013.

MK_QSL
MVP
MVP

Try to use MonthYear instead of Month used in that post.

Otherwise, upload your sample file...

jonathandienst
Partner
Partner

Hi

I assume you want to see the monthly (in column 1) as well as the cumulative, and the average:

Cumulative:

     =RangeSum(Column(1), 0, RowNo())

    

Average:

     =RangeSum(Column(1), 0, RowNo()) / RowNo()

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan, I dont believe this will work as how would I maintain my original set of dimensions and then for each dimension (month) go back 12 months to do the sum?