Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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! 

29 Replies
sunny_talwar

Would you be able to share the app where you are trying this?

jcampbell474
Creator III
Creator III
Author

Sure thing, Sunny.  App is attached.

sunny_talwar

Do you know which of these three numbers are not correct?

Capture.PNG

sunny_talwar

I am attaching an Excel file where I calculated 99.3% based on raw data... would you be able to point out which of the three numbers are causing the issue

Capture.PNG

jcampbell474
Creator III
Creator III
Author

Sunny, this is what I get when I 'do a favor' for another department.

I don't know their data.  Reloaded/exported and got entirely different numbers.  Apparently, it changes.  Thought I was going crazy.  I arrived at 99.6% earlier in the day. 

My apologies for your time spent looking at this.  I surely do appreciate your help.  Thank you!

Capturer12c.PNG

sunny_talwar

So, essentially first you do a RangeAvg() and then a RangeSum over that RangeAvg? I still didn't get 99.6% because  my numbers for Closed Reserves matched untill Jan 2016 (from Aug 2016 to Jan 2016), but not before that....

=RangeSum(Above(RangeAvg(Above(Sum({<MonthName,Year, Month>}[Closed Reserves]), 0, 12)), 0, 12))

/RangeSum(Above(RangeAvg(Above(Sum({<MonthName,Year, Month>}[New Reserves] + [Reopen Reserves]), 0, 12)), 0, 12)) * Avg(1)

See if this looks like what you want?

Capture.PNG

jcampbell474
Creator III
Creator III
Author

Yes, sir.  That's exactly what I need.  I would have never thought about doing a RangeSum over the RangeAvg.  Question: What does the * Avg(1) do?  Is it 'brute forcing' the expression to behave a certain way?

sunny_talwar

You see how the expressions without the Avg(1) shows everything regardless of the selection? This happens because we ignore selection in Date and Month related field... now to get rid of the un-selected value we multiply by Avg(1). Avg(1) will equal to 1 within the selection and 0 outside of the selection. We can use an if statement to do the same thing, but using Avg(1) is supposedly efficient compared to if statement.

jcampbell474
Creator III
Creator III
Author

Ok, I think I understand.  It basically filters the results.  Multiplying by Avg(1) causes it either be 0 or > 0, effectively removing the un-selected value(s).  Seems like it could be more efficient than an If statement.  I will try to start using it. 

Thank you for the explanation and help!!!   

IvanMtz
Contributor
Contributor

Hello Sunny 

 

I just wondering that the way you present the data is like an app how do you do that? lol is just that very new on qlik

 

Thank you