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!
When you say Month, do you mean MonthName field or another Month field which is just Jan, Feb, Mar field without year?
What does {<MonthName, Year>} do?
It just ignore selection in MonthName and Year field which makes it possible for your expression to look for Avg for Month Years which are out of selection
The name of the field is actually Month. It's just Jan, Feb, Mar, etc...
So you select a Month and a Year or just Month? If you select Month and no year, which 12 months it shows?
It shows the selected month, for each year. E.g., Mar 2011, Mar 2012, Mar 2013...
Jason I am confused... what do you want to see? Based on selection in Month field, what should be the output you are looking to get?
My apologies for the confusion. I must admit, I'm confused with this request too, so I might not be communicating it correctly.
Here's what I'm looking for:
If I select 2016 and Jun, the Rolling 12 average should be $4,800,411. But, it shows $2,589,814.
Do you mean Jun or Jan?
Jan I get this
June I get this
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.
Ok, got it. Thank you for your patience.
I did mean Jan 2016, not June.
Looks like that did the trick. Many thanks!!!
No problem... I am glad we were able to figure it out.
Best,
Sunny
Sunny, sorry to bother you again. Everything works...until I get to percentages.
I can't figure out how to modify the expression to be accurate when dividing.
Example: I thought this expression would be correct, but it doesn't actually give me the true previous 12-mth average.
(RangeAvg(Above(Sum({<MonthName,Year,Month>}[Closed Reserves]), 0, 12)) * Avg(1))
/ (RangeAvg(Above(Sum({<MonthName,Year,Month>}[New Reserves] + [Reopen Reserves]), 0, 12)) * Avg(1))
MonthName | Close Pcnt | Closed Reserves | New Reserves | Reopen Reserves | True Avg % |
Sep 2015 | 106.5% | 46,456 | 43,648 | 2,977 | 99.6% |
Oct 2015 | 98.6% | 47,760 | 44,796 | 3,081 | 99.8% |
Nov 2015 | 99.0% | 48,535 | 45,519 | 3,152 | 99.7% |
Dec 2015 | 100.2% | 49,214 | 45,994 | 3,198 | 100.0% |
Jan 2016 | 96.4% | 49,670 | 46,533 | 3,255 | 99.8% |
Feb 2016 | 100.7% | 50,193 | 47,116 | 3,289 | 99.6% |
Mar 2016 | 103.0% | 50,735 | 47,498 | 3,315 | 99.8% |
Apr 2016 | 98.1% | 52,284 | 49,011 | 3,447 | 99.7% |
May 2016 | 101.2% | 51,808 | 48,546 | 3,435 | 99.7% |
Jun 2016 | 95.5% | 52,160 | 48,977 | 3,468 | 99.5% |
Jul 2016 | 96.6% | 52,611 | 49,474 | 3,483 | 99.3% |
Aug 2016 | 97.7% | 53,027 | 49,911 | 3,502 | 99.3% |
The 'True' Prev 12-Month Avg % should be 99.6%. The expression above gives 99.3%.
I don't know what Avg(1) is doing, but know that it does affect the quotient. Should the entire expression be constructed differently to achieve the needed result?