Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
wschefter
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
dwforest
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]),

View solution in original post

3 Replies
dwforest
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]),

View solution in original post

Re: Rolling average for previous 12 months as a percentage

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

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