I have a set of data with dates from April 2016 to February 2018. I need to evaluate the mean of the values in all of the complete twelve month periods in the data e.g. for Apr 16 to Mar 17 and then for May 16 to Apr 17 and so on up to Mar 17 - Feb 18. (I also have to calculate the median of those sets)
What's the best way to do it?
Solved! Go to Solution.
What you can do is pivot your dataset so you have a row for each month instead of column for each month.
You can then create a tag which represents the appropriate fiscal period for that month, probably using a formula tool.
Finally you can use the summerize tool, grouping by your new fiscal period field, and summerizing your value column in as many ways as you like (min, max, median, mode, mean, sum, etc.).
Happy to build an example out if you have some sample data.
Ben
If you want to calculate the rolling average for the period you can use the multirow formula tool...something like this:
Part time Tableau, Part Time Alteryx. Full Time Awesome
Agree with @LordNeilLord, I misinterpreted the question.
With my new understanding (thanks @LordNeilLord), here is my suggestion.
This is easily scalable to look back n number of months (currently set to 12) and across different measures.
See attached workflow.
Ben
Simple when you know how.
Thank you very much
@LordNeilLordwrote:
If you want to calculate the rolling average for the period you can use the multirow formula tool...something like this:
Part time Tableau, Part Time Alteryx. Full Time Awesome
Hello,
I am trying to do some calculations on data that looks like below. I want to take the average of the latest 2 days of sales and compare it to the average of the previous 5 days. Trying to use a multi row formula. Can you help please?
Date | Sales |
28/02/2020 | 9 |
29/02/2020 | 8 |
01/03/2020 | 10 |
02/03/2020 | 8 |
03/03/2020 | 9 |
04/03/2020 | 15 |
05/03/2020 | 22 |
06/03/2020 | 20 |