1 Reply Latest reply on Apr 14, 2016 3:32 PM by diego.medrano

    Rolling Average Outside of Window - Combining Fixed with WindowAvg Equations

    lynette.chen.2

      I'm trying to calculate a rolling index average that utilizes a window outside of the filter range. My filter has to be a global filter.

       

      I have been using ((WINDOW_AVG(SUM([Toys]),0,0))/WINDOW_AVG(SUM([Toys]),-364,0))*100 to get my rolling index average for the year. Since I need to rolling average to include dates  outside the filter range, I already tried the option DATE(LOOKUP(MIN([Date]),0)) and putting it on the date shelf. The problem is that I need to have a date global filter and this no longer works.


      I thought about using a fixed equation to anchor my dates DATE({ FIXED :(MIN([Date]))}) but don't know how to use it in conjunction with the WINDOW_AVG equation.

       

      I have also tried duplicating my data source and joining based on a unique string. However, the date filter from my primary source continues to affect the rolling index average calculation in my secondary data source.

       

      ASAP responses greatly appreciated.

       

      Thanks!