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.
Solved! Go to Solution.
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:
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!
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!
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
I was able to resolve my issues and get the update working, just FYI. Thanks
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!