Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jcampbell474
Creator III
Creator III

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! 

29 Replies
sunny_talwar

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

jcampbell474
Creator III
Creator III
Author

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

sunny_talwar

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

jcampbell474
Creator III
Creator III
Author

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

sunny_talwar

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?

Capture.PNG

jcampbell474
Creator III
Creator III
Author

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.

sunny_talwar

Do you mean Jun or Jan?

Jan I get this

Capture.PNG

June I get this

Capture.PNG

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.

jcampbell474
Creator III
Creator III
Author

Ok, got it.  Thank you for your patience.

I did mean Jan 2016, not June. 

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

sunny_talwar

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

Best,

Sunny

jcampbell474
Creator III
Creator III
Author

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

     

MonthNameClose PcntClosed ReservesNew ReservesReopen ReservesTrue Avg %
Sep 2015106.5%46,45643,6482,97799.6%
Oct 201598.6%47,76044,7963,08199.8%
Nov 201599.0%48,53545,5193,15299.7%
Dec 2015100.2%49,21445,9943,198100.0%
Jan 201696.4%49,67046,5333,25599.8%
Feb 2016100.7%50,19347,1163,28999.6%
Mar 2016103.0%50,73547,4983,31599.8%
Apr 201698.1%52,28449,0113,44799.7%
May 2016101.2%51,80848,5463,43599.7%
Jun 201695.5%52,16048,9773,46899.5%
Jul 201696.6%52,61149,4743,48399.3%
Aug 201697.7%53,02749,9113,50299.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?