Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell474
Contributor III

Rolling 12-Months Average

Hello all,

I’ve been tasked to create a Rolling 12-month average chart.  I haven’t been able to find a solution from my research in the QV Community over the past 2-days.  Hoping someone here can share their expertise.

I think one of the requirements is what makes it challenging.  If the user selects say, three months, they need to see the average of the prior 12-months on each month.  So, a rolling 12-months for multiple months (selected). 

They need to see it in a bar chart with either a second series for the rolling-12 average or a trendline of it. 

I initially thought about using the asof approach, but the application is 6+gb.  We can’t intentionally make it that much larger. 

I have attached a sample. Didn’t load data for all of the expressions, but most of it is there.  Can someone guide me to the best method of meeting this requirement?

Thanks! 

1 Solution

Accepted Solutions

Re: Rolling 12-Months Average

Do you mean Jun or Jan?

Jan I get this

Capture.PNG

June I get this

Capture.PNG

Expression used:

RangeAvg(Above(Sum({<MonthName, Year, Month>}[Expenses Paid]), 0, 12)) * Avg(1)

Like I mentioned above, ignore all date/time related selection where you plan to make selections, or expect the users to make selections.

28 Replies

Re: Rolling 12-Months Average

may be try this..

haven't tested

RangeSum(Below(Total SUM([Expenses Paid]),0,12))

or

RangeSum(Above(Total SUM([Expenses Paid]),0,12))

jcampbell474
Contributor III

Re: Rolling 12-Months Average

Thank you.  I need an average (rolling 12).  Should it be RangeAvg?

Here's a screenshot using RangeAvg.  Looks like it works using the max date selected - 12 back.  The rolling 12 avg isn't accurate for Jan 2016.

R12.PNG

Re: Rolling 12-Months Average

May be try this:

RangeAvg(Above(Sum({<MonthName>}[Expenses Paid]), 0, RowNo())) * Avg(1)

jcampbell474
Contributor III

Re: Rolling 12-Months Average

Thank you, Sunny.

That works, but only for the selected range.  Appears to be a cumulative average.

The output should be the prior 12-months average, per (selected) month.  If more than one month is selected, the average of the prior 12-months should show for each month.  It's another series in the chart. 

I'm thinking it's something like this, but it doesn't work:

sum({<%Date = {">=$(=MonthStart(Max(%Date),-12))<=$(=MonthStart(Max(%Date)))"},Month=>} (AGGR([Expenses Paid],MonthName))) * Avg(1)

(Note: I don't know what the Avg(1) does...or if AGGR can be used here.  QV says the expression is ok.)

Example:

Jan 2016 - avg of Feb 2015 thru Jan 2016

Feb 2016 - avg of Mar 2015 thru Feb 2016

etc...

Re: Rolling 12-Months Average

Totally forgot about the 12 month part... can you try this

RangeAvg(Above(Sum({<MonthName>}[Expenses Paid]), 0, 12)) * Avg(1)

stonecold111
Contributor

Re: Rolling 12-Months Average

why you are multiplying with avg(1)

jcampbell474
Contributor III

Re: Rolling 12-Months Average

Thanks, Sunny.  I tried it and nothing changed. The date range in the expression doesn't go outside of the dimensions - making set analysis required. Something like: avg({<%Date = {">=$(=MonthStart(Max(%Date),-12))<=$(=MonthStart(Max(%Date)))"}>} [Expenses Paid])

Suresh, I don't know why it's multiplied by an avg(1).

An updated copy of the application is attached to help visualize the request.

This might help also. 

R12c.PNG

Re: Rolling 12-Months Average

Does this look right?

Capture.PNG

Expression

RangeAvg(Above(Sum({<MonthName, Year>}[Expenses Paid]), 0, 12)) * Avg(1)

Basically, you need to ignore selection in all date and year related fields where you plan to make a selection. In the attached example, you made selection in Year, but you did not exclude the selection in Year field....

jcampbell474
Contributor III

Re: Rolling 12-Months Average

Yes, the math is spot-on.  Thank you!

My only concern is not being able to select a Month to get the rolling-12 for a (selected) given month.  Is there a way to have it avg back 12-months from each (selected) month?  If not, we'll deal with it.  What does {<MonthName, Year>} do? 

Community Browser