3 Replies Latest reply: Jun 14, 2018 11:08 AM by Will Schefter RSS

    Rolling average for previous 12 months as a percentage

    Will Schefter

      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.