7 Replies Latest reply on Aug 22, 2018 10:53 PM by ArseneXie

    Rolling Average with Zero Records

    jason.hodge

      I'm attempting to create a line graph showing a twelve month rolling average count of records on a month by month basis.  I found the WINDOW_AVG feature, but the issue that I run into is that not all months have records.  For example, let's say I have twelve records for June 2018, and no records for any other month.  All the chart shows is a single mark at ten for that month, and then no marks anywhere else.  Basically, this means that my average shows as 12 at June 2018, and then is missing data everywhere else, when in reality the rolling average should be 1 (12 records/12 months).  The WINDOW_AVG works, but only if another record pops up at a later date, then it will correctly calculate the running average and place a line between the two known points.

       

      I've tried using the ZN function, but it appears that the count does not show up as null, there's no record in the first place.  Does anyone know a workaround for this?  For months with no records, what I actually want the moving average calculation to use is 0.

       

      My current pill on the columns shelf shows as: WINDOW_AVG(COUNT([Number of Records]), -12, 0)

       

      Rows shelf is a Date Value by Month