28 Replies Latest reply: Apr 12, 2017 7:58 AM by Jason Campbell

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!

• Re: Rolling 12-Months Average

may be try this..

haven't tested

RangeSum(Below(Total SUM([Expenses Paid]),0,12))

or

RangeSum(Above(Total SUM([Expenses Paid]),0,12))

• Re: Rolling 12-Months Average

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.

• Re: Rolling 12-Months Average

May be try this:

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

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

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

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

• Re: Rolling 12-Months Average

why you are multiplying with avg(1)

• Re: Rolling 12-Months Average

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.

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

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?

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

The name of the field is actually Month. It's just Jan, Feb, Mar, etc...

• Re: Rolling 12-Months Average

So you select a Month and a Year or just Month? If you select Month and no year, which 12 months it shows?

• Re: Rolling 12-Months Average

It shows the selected month, for each year.  E.g., Mar 2011, Mar 2012, Mar 2013...

• Re: Rolling 12-Months Average

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?

• Re: Rolling 12-Months Average

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.

• Re: Rolling 12-Months Average

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.

• Re: Rolling 12-Months Average

Ok, got it.  Thank you for your patience.

I did mean Jan 2016, not June.

Looks like that did the trick.  Many thanks!!!

• Re: Rolling 12-Months Average

No problem... I am glad we were able to figure it out.

Best,

Sunny

• Re: Rolling 12-Months Average

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?

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

Sure thing, Sunny.  App is attached.

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

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

• Re: Rolling 12-Months Average

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!

• Re: Rolling 12-Months Average

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?

• Re: Rolling 12-Months Average

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?

• Re: Rolling 12-Months Average

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.

• Re: Rolling 12-Months Average

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