Forum Discussion
Cleaning bad time series data
I am trying to clean time series data, which is impact by upstream processes whcih result in extreme errors. Intially a large outlier will flow into the series, and then this move is reversed the nex...
Hi Amy,
I did come up with a solution, but it isn't very flexible, and only catches one blip in the data. Basically, I filtered for outliers, zeroing out bad rows. Then I created a cummulative sum of the outliers , placing the final sum (4 million in below), into clean data column.
| Date | Raw Data | Clean Data |
7/1/2019 | 19590.02 | 19590.02 |
| 7/3/2019 | 1561.33 | 1561.33 |
| 7/5/2019 | -1500003 | -1500003 |
| 7/8/2019 | -25000 | -25000 |
| 7/9/2019 | 35000 | 35000 |
| 7/10/2019 | -2000 | -2000 |
| 7/11/2019 | 180000 | 180000 |
| 7/12/2019 | 250000 | 250000 |
| 7/15/2019 | -5.2E+11 | 0 |
| 7/16/2019 | 5.20004E+11 | 4000000 |
| 7/17/2019 | 320000 | 320000 |
| 7/18/2019 | 650000 | 650000 |
| 7/19/2019 | -250000 | -250000 |
How about if you force the column in datatype "Fixed decimal number" and then do the aggregation?
Step: In the Edit queries, click on the field, go to modelling tab and under data type tab choose "fixed decimal number".
yeah it is already, that's just cut and paste formatting from excel.