topic Re: Rolling average for previous 12 months as a percentage in App Development

Rolling average for previous 12 months as a percentage

wschefter — Wed, 13 Jun 2018 21:28:55 GMT

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

Re: Rolling average for previous 12 months as a percentage

dwforest — Wed, 13 Jun 2018 21:32:38 GMT

Did you try

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

Re: Rolling average for previous 12 months as a percentage

sunny_talwar — Thu, 14 Jun 2018 01:53:28 GMT

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

Re: Rolling average for previous 12 months as a percentage

wschefter — Thu, 14 Jun 2018 15:08:26 GMT

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.