Qlik Sense App Development

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
New Contributor II

Rolling average for previous 12 months as a percentage

Hi all;

I am trying to write an expression that will look at the previous 12 months of data and provide an average for those 12 months as a percentage.  My data set is 79 months, roughly 341k rows of data.

I can write the expression to look at only the previous month's average easily, Avg({<[=[Created on.autoCalendar.MonthsAgo]={1}>} 1-[Late to Confirm]), but I am struggling to write the expression for the average of 12 months.

I have read through several other posts and have copied those expressions over and, while they work, I most definitely do not get the correct answer.  An example is this:  RangeAvg(Above(Sum({<[Created on]>} [Late to Confirm]), 0, 12)) * Avg(1).  This expression gave me 4,722,300.0%.

Here are my fields:

Created on

Late to Request

Created on is each date an order is created which is why I have so many rows of data.  For any given date there could be dozens upon dozens of lines created.

Late to Request is binary.  0 is on time, 1 is late.

Any help would be much appreciated.

Thanks.

Will

1 Solution

Accepted Solutions
Valued Contributor

Re: Rolling average for previous 12 months as a percentage

Did you try

Avg({<[=[Created on.autoCalendar.MonthsAgo]={'<=12'}>} 1-[Late to Confirm]),

3 Replies
Valued Contributor

Re: Rolling average for previous 12 months as a percentage

Did you try

Avg({<[=[Created on.autoCalendar.MonthsAgo]={'<=12'}>} 1-[Late to Confirm]),

MVP

Re: Rolling average for previous 12 months as a percentage

May be share a sample with expected output to help you better

Highlighted
New Contributor II

Re: Rolling average for previous 12 months as a percentage

Thanks guys.  I don't know why I didn't think of <= but that was it.  Once my brain starts functioning correctly this all will come as second nature.