topic Re: Creating MoM, YoY and YTD calculations for multiple KPIs in Alteryx Designer Desktop Discussions

Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Thu, 24 May 2018 04:23:47 GMT

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.

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

JoshKushner — Tue, 31 Oct 2017 07:04:59 GMT

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:

Mean Price (Sale, Listing, etc.):

Use the summarize tool

Prior Month:

Use the multi-row formula tool

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.)

YTD (Assuming mean price YTD):

Use the multi-row formula tool twice (one create record ID's, the other to take mean)

Flow:

Increment Record ID's:

Calculate YTD:

MoM (%):

Once we collect prior month price this is easy. Use the formula tool.

Flow:

Formula:

YoY (%):

Flow:

Formula:

YTD (%):

Flow:

Formula:

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Tue, 31 Oct 2017 07:09:33 GMT

Hey Josh!

Wow I appreciate the information. I went ahead and exported as you recommended, in case this changes some of the step you provided me.

Thanks again for all of the info. I'll review it first thing in the AM when I'm fresh eyed!

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

JoshKushner — Tue, 31 Oct 2017 08:36:26 GMT

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:

Results:

Transpose:

Cross Tab:

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Tue, 31 Oct 2017 17:31:34 GMT

Hey Josh,

This is probably the most comprehensive and helpful response in a forum I've ever received. Both extremely helpful in learning new processes and will save my countless hours banging my head on the desk. GREATLY appreciated.

I am not able to open the packaged workflow however. Looks like I'm running version 10.6.8.17850 of Alteryx. Is it possible for you to downscale and resave?

Again, thank you very much for the detailed step by step.

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

JoshKushner — Tue, 31 Oct 2017 22:19:04 GMT

Happy to help!

Try this attachment. I followed the steps here to try and turn it into v10.5. Let me know if you need a prior version from that. (You might be able to adjust the version using it as well if it still doesn't work)

(attaching both a regular workbook and the extract just in case)

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Tue, 31 Oct 2017 22:25:39 GMT

The regular workbook opened, but not the extract. Workbooks works fine.

Ok I'm going to dig into the provided workbook and then I'll try reduplicating the process using your instructions so I get a better understanding of how you did it.

Again, thank you!

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

jdunkerley79 — Thu, 02 Nov 2017 08:17:11 GMT

I thought I would show a small adjustment to @JoshKushner workflow.

The principles are exactly the same but it does each period in parallel. The required periods are defined in a text input:

and the starting date is computed using a formula tool

After this, the RefDate is joined back to data on Date to get RefValue.

Only a minor change from the great work @JoshKushner did but felt worth sharing.

Workflow attached in 10.5 format

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Thu, 02 Nov 2017 22:19:46 GMT

@jdunkerley79 thanks for the Alt version. So does this reduce run time and simplify the stream a bit? Really looking forward to gaining more experience with alteryx.

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

jdunkerley79 — Fri, 03 Nov 2017 07:57:07 GMT

I expect the performance to be similar. If anything it may be slightly slower as it will involve slightly more sorts than @JoshKushner

My reasoning for my tweaks is:

- doesn't require all months to present

- easier to add another period (e.g if you wanted quarter very small change)

- by having less tools easier to debug

That being said had missed a field on the join so had a bug. New version attached with it corrected.

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Sat, 25 Nov 2017 21:01:11 GMT

Hi Josh,

There was an update to the formatting of the input data. I've attempted to understand the steps outline here and modify the existing workbook with the additional data columns (State, City). Right now they are not populated with data, but will be in the future.

Few questions:

Would you mind spot checking this to see if I have it set up right? I didn't have it run results on them yet, due to lack of data. When I do, I just add to the cross tabs?
I'm seeing results outputted in columns that should't be able to yield a results. For YoY calculations, shouldn't the differences be 100% or the exact number of the comparison since there is no prior year data.
Month Prior results are showing number from the beginning month. That shouldn't be possible as well since there was no month prior data for the beginning month.
The workflow is going to be ran on a very large data input of every MSA and ZIP in the US. Right now it, its about a 55 second for 3 MSA's. What's the best way to handle large runs like this? I do have access to Alteryx Server in the coming month.
Would it be best to post additional questions on this workflow in this thread or start a new question thread?

I've attached a sample of the Phoenix MSA level results and highlighted some data that looks like the results might be off.

Also attachment the updated packaged workflow. Thanks!

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Sat, 25 Nov 2017 21:04:25 GMT

Additional resource highlighting potential calculation issues.

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Sat, 27 Jan 2018 20:46:52 GMT

@JoshKushner

Hey Josh,

I've obtained the entire US level data consisting over over 3 million records. Granularity, it goes down to zip code level. This is going to be a very large run as it goes back 5 years of monthly data.

Before I run, could I trouble you to review my latest workflow? I'm having some issue updating it to insure all of the MoM and YoY calculations are correct and also proving those stats for the State, City and Zip level of granularity. I've attached my latest workbook. I can spot check the results against local MLS stats here in Phoenix to validate the output results.

I'm extremely appreciative of any assistance. Thanks!

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

jjc42 — Mon, 23 Apr 2018 21:43:17 GMT

@jdunkerley79,

Thanks for posting the workflow. I'm having some difficulty calculating Prior YTD as the RefDate field shows the first month of the year for the value in the Date column. Depending on the metric I think it should show a sum of the prior months in that year or just the value from the same month a year ago. How would I go about adjusting the workflow to reflect the Prior YTD number?

Thanks,

John

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

BigDataGeek — Mon, 23 Apr 2018 22:08:43 GMT

I was able to resolve my issues and get the update working, just FYI. Thanks

Re: Creating MoM, YoY and YTD calculations for multiple KPIs

JacobLuke — Wed, 01 Dec 2021 14:59:46 GMT

Hey @JoshKushner, is there a way to calculate the mean price YTD using this setup while also ignoring null instances?

For ex:

Jan - 3

Feb - 4

Mar - NULL

YTD - 3.5

Any help or alternatives would be greatly appreciated!