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!
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.
may be try this..
haven't tested
RangeSum(Below(Total SUM([Expenses Paid]),0,12))
or
RangeSum(Above(Total SUM([Expenses Paid]),0,12))
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.
May be try this:
RangeAvg(Above(Sum({<MonthName>}[Expenses Paid]), 0, RowNo())) * Avg(1)
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...
Totally forgot about the 12 month part... can you try this
RangeAvg(Above(Sum({<MonthName>}[Expenses Paid]), 0, 12)) * Avg(1)
why you are multiplying with avg(1)
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.
Does this look right?
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....
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?