Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek

Hey All,

I'm new to Alteryx, especially with creating and aggregating data within it, so bare with me.

I've got an excel data set I'd like to develop MoM, YoY and YTD calculations for each row of data.

My goals:

- Create these calculations for all KPI columns (median sale price, median listing price, etc)

- Add field for 'Prior Month', 'Prior Year Month' and 'YTD' that display the actual numbers from those field.

- Add fields for 'MoM', 'YoY, and 'YTD' as a percentage change.

- The data is either ZIP code level OR MSA level. If no Zip, then its already aggregated to MSA

- Same applies for Year, except we compare it to the prior year, same month and develop a percentage change.

- For YTD, its YTD average / YTD Average (given same months) of year prior.

I've attached the data in a workflow for review. Again, I'm new to this all, but willing to work to understand it all if someone could help with some best practices in this area and how to.

Thanks!

I'm trying to avoid calculating these in Tableau to increase dashboard speeds.

RB Workflow.yxmd

Best Practices

Accepted answers

JoshKushner

The formulas covered in the first part will get you most of the way there. You have so many columns that all of them need to be processed with the transpose tool and cross tab tool.

(An essential part of transpose/cross tab is add a record ID tool right before so everything lines up at the end)

I've attached the modified workflow.

Flow:

Flow 1.PNG Flow 2.PNG

Results:

Transpose:

Cross Tab:

RB Workflow.yxzp

All comments

JoshKushner

Hi Shap,

In the future you'll want to export your workbook otherwise we can't see the data you're reading.

Export:

Without knowing your data set I can't be specific, but the general calculations you're looking for are below:

(I've used random sample data)

Sample Data:

input data.PNG

Mean Price (Sale, Listing, etc.):

Use the summarize tool

sum r.PNG

Prior Month:

Use the multi-row formula tool

multi row.PNG

multi row r.PNG

Prior Year Month:

Now this can get tricky. If your data is organized like the sample data set you can use the multi row formula tool referencing the price 12 months ago --> [Row-12:Price].

If not, it's more complicated (we don't know if there's one price or multiple prices per month. If so, will we take the min, mean, or max price. We'll need a separate formula(s) to determine the record ID. etc.)