1 Reply Latest reply on Feb 13, 2017 3:36 PM by diego.medrano

    Aggregating a table calculation - or LOD alternative?

    Arran McInally

      Hi all, I cannot seem to find anything related to this question so I am hoping someone can help!

       

      For a series of data I want to implement a rolling average calculation across the last 30 days or so and then take the rolling standard deviation for the same date range.

      With the above data I want to count the number of outliers in a month (defined by the daily datapoint falling outside of the rolling average +/- 1 * rolling SD).

       

      So far:

      Rolling_average (computed across table on d/m/y):

      WINDOW_AVERAGE(sum([NormalisedValue),-30,0)

       

      Rolling_SD (computed across table on d/m/y):

      WINDOW_STDEVP(sum([NormalisedValue),-30,0)

       

      From here my 'logical' step would say to find/count an outlier for the day:

      {FIXED [Date] : IF SUM(NormalisedValue) > Rolling_Average+Rolling_SD or SUM(NormalisedValue) < Rolling_Average-Rolling_SD THEN 1 ELSE 0 END}

       

      And then sum the above result over each month with the intent to get the result:

       

      MonthCount of Outliers
      Nov 20164
      Dec 20161
      Jan 201711
      Feb 20172

       

      The problem with the above is there doesn't seem to be a method of using the result of a table calculation in a new sheet. The rolling average/SD needs a visible dimension to compute across. Is there a way to use the result of a table calculation in a new sheet?

       

       

      Alternatively (and possibly preferentially) is there a way to replicate the Rolling_Average and Rolling_SD with LOD expressions or a similar non-tablecalculation function?

       

      Cheers!