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:
Month | Count of Outliers |
---|---|
Nov 2016 | 4 |
Dec 2016 | 1 |
Jan 2017 | 11 |
Feb 2017 | 2 |
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!
Hey Arran,
Do any of the suggestions here help?
use calculated results on another sheet?
If not, providing a packaged workbook with sample data may make it easier for other users to give you a helpful response.
Thanks,
-Diego
Become a Viz Whiz on the Forums!
Support the Community and master Tableau.