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):



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



      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?