Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator 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
sunny_talwar

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.

View solution in original post

28 Replies
MK_QSL
MVP
MVP

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
Creator III
Creator III
Author

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

sunny_talwar

May be try this:

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

jcampbell474
Creator III
Creator III
Author

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...

sunny_talwar

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

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

stonecold111
Creator III
Creator III

why you are multiplying with avg(1)

jcampbell474
Creator III
Creator III
Author

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

sunny_talwar

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
Creator III
Creator III
Author

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?