Performing serial calculations on a range

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?

Iterative Macro

Accepted answers

LordNeilLord

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

Data Lover

RollingAverage.yxzp

All comments

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

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

Data Lover

RollingAverage.yxzp

BenMoss

Agree with @LordNeilLord, I misinterpreted the question.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703