topic Re: Rolling 12-Months Average in QlikView

Rolling 12-Months Average

jcampbell474 — Fri, 07 Apr 2017 13:52:36 GMT

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

MK_QSL — Fri, 07 Apr 2017 13:58:21 GMT

may be try this..

haven't tested

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

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

Re: Rolling 12-Months Average

jcampbell474 — Fri, 07 Apr 2017 16:03:00 GMT

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

sunny_talwar — Fri, 07 Apr 2017 16:07:00 GMT

May be try this:

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

Re: Rolling 12-Months Average

jcampbell474 — Fri, 07 Apr 2017 19:16:20 GMT

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

sunny_talwar — Sat, 08 Apr 2017 10:59:47 GMT

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

stonecold111 — Mon, 10 Apr 2017 12:11:55 GMT

why you are multiplying with avg(1)

Re: Rolling 12-Months Average

jcampbell474 — Mon, 10 Apr 2017 12:29:30 GMT

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

sunny_talwar — Mon, 10 Apr 2017 12:39:41 GMT

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

jcampbell474 — Mon, 10 Apr 2017 13:10:57 GMT

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

sunny_talwar — Mon, 10 Apr 2017 13:13:34 GMT

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

jcampbell474 — Mon, 10 Apr 2017 14:24:50 GMT

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

Re: Rolling 12-Months Average

sunny_talwar — Mon, 10 Apr 2017 14:27:06 GMT

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

jcampbell474 — Mon, 10 Apr 2017 16:00:51 GMT

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

Re: Rolling 12-Months Average

sunny_talwar — Mon, 10 Apr 2017 16:07:15 GMT

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

jcampbell474 — Mon, 10 Apr 2017 16:12:04 GMT

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

sunny_talwar — Mon, 10 Apr 2017 16:17:18 GMT

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

jcampbell474 — Mon, 10 Apr 2017 17:06:57 GMT

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

sunny_talwar — Mon, 10 Apr 2017 17:12:29 GMT

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

Best,

Sunny

Re: Rolling 12-Months Average

jcampbell474 — Tue, 11 Apr 2017 16:09:56 GMT

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?