Forum Discussion
Rolling Average Quick Measure Incorrectly Calculating the Future
Anyone solved this yet? What I (and I believe the others in this forum) am trying to creat is technically a 'Trailing Average' and not a Rolling Average. A Trailing Average ceases on the last date on which data exists. A rolling average can continue on forever (even though it will go to zero for dates that are far enough into the future. There is no 'trailing avereage' quick measure, so I have been trying to make the Rolling Average Quick Measure bend to my needs. I'm close, but this has been vexing me as well since I have been using the Rolling Average Quick Measure. It's tough to explain to data consumers why there is trailing average data for dates that have yet to record any data and absolutely creates doubt in their minds as to the validity of the underlying calculation (even though it is technically correct). I believe it has to do with the fact that the LASTDATE call in the quick measure is returning the last date of the year (12/31/2018 in my case) as opposed to the last date in the dataset (usually within a day or two of today). I too do not want to handle via limiting the X-axis in graphs/visualizations as I am constantly changing the Rolling Period for analysis (7, 14, 21, 28 days) and would need to reset the X-axis every time. I'd like to find a way to 'force' the trailing average calculation to stop at the latest date in the data set and not that latest date for which the calculation is 'zero'. Any help would be greatly appreciated.
- Anonymous8 years agoNot applicable
In my specific case I was able to address this indirectrly. I added a measure that COUNTs the metric that is being displayed in the rolling avg. Then I added an advanced filter for each visualization for that COUNT measure that will only show items of that metric that are over zero. This trims the date automatically to the last date with real data in the set. I hope it helps others.