Calculating a 3 month average and YoY Growth

[spencd2]

I'm trying to recreate some equations I made in excel in Alteryx.

I have a table with Revenue numbers by month. For each month, I'd like to calculate the 3-month average revenue (Average of current month + the last 2 months). See the attachment for an example and the below screenshot.

I take that 3 month average and the calculate a YoY growth rate (i.e. the growth of the January2020 3-month Average to the January2021 3-Month Average)  - I would also like to recreate that equation in Alteryx. 

So ultimately for each Month I would have the following metrics

Revenue

3 Month Revenue Average 

YoY Trailing 3 Month Growth Rate

Any Ideas? Appreciate the help

[Qiu · Accepted solution]

@spencd2 
I think this is a very practical data preparation issue.
hope I understand your intention correctly.

[DawnDuong]

hi @spencd2 

@Qiu  solution is already elegant and clean.

Since you are probably a user of tableau as well (i see from the file name), I thought it is worthwhile to mention that the optimal data structure for this case is "narrow and thin" just like in Tableau.

Just like in Tableau where you need to transpose the data to have the "date" as a column before you can do all the table calculation, the first step in many Excel-based time series is to transpose the data so that you can have a "narrow and thin" datatable. Best practice for data involving date or datetime data is to format them to date/datetime by the DateTime tool or otherwise.

For Excel users, especially involving finance use cases where taking interval-based sum or average is commonly required, the Multe-Row tool is very handy AFTER the data has been transposed.
Cheers,
Dawn.

[spencd2]

Thanks @DawnDuong. I want to make sure I understand what you're saying by a "narrow and thin" data structure. I assume you mean rather than having individual columns for each date, you would have a "Date' field, with a line item for each time period (in this instance, MM/YYYY)?

"Best practice for data involving date or datetime data is to format them to date/datetime by the DateTime tool or otherwise."

Can you elaborate what you mean here? @Qiu solution looks great (thanks you!) and mirrors what I have in excel, but the data I'm actually working on is structured as I described (with "Date" as a field; the excel file is just a sample to disguise private data) 

Any suggestions on how to structure @Qiu 's workflow to that format would be appreciated!

[spencd2]

Thank you so much @Qiu ! This looks to be exactly what I'm after. 

[DawnDuong]

hi @spencd2 

Yes, your understanding is correct. If your data is already in the "narrow and tall" format, with date as a field, there is no need to do the transpose step. If the date field is already correctly formatted as YYYY-MM-DD, then you can start with the Multi-Row tool right away. I assume you are familiar with the tools used in @Qiu 's workflows - if not, do check out the interactive lessons, these bite-sized modules are very good.

If you have a truncated and sanitised data in the original "narrow and tall" format, you can attach here and @Qiu  probably can update the workflows in a zip!

Dawn.