Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Would you be able to share the app where you are trying this?
Sure thing, Sunny. App is attached.
Do you know which of these three numbers are not correct?
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
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!
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?
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?
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.
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!!!
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