Performing serial calculations on a range

[NotQuiteClueless]

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?

[BenMoss]

Hi @NotQuiteClueless,

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

[LordNeilLord · Accepted solution]

Hey @NotQuiteClueless

If you want to calculate the rolling average for the period you can use the multirow formula tool...something like this:

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[BenMoss]

Agree with @LordNeilLord, I misinterpreted the question.

[BenMoss]

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

[NotQuiteClueless]

Simple when you know how.

Thank you very much

---

@LordNeilLordwrote:

Hey @NotQuiteClueless

If you want to calculate the rolling average for the period you can use the multirow formula tool...something like this:

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

---

[Usamah22]

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