Calculating a YoY Change field for each Quarter at specific Level of Detail

[paulwini]

Hi Everyone,

I'm very new to Alteryx, and I was hoping for some guidance on a calculated field I would like to add to my workflow. I have an Excel dataset structured like so: 

I would like to create a field that shows the YoY change in Count at the Product & Color level. Each Product & Color have only one row of data per Quarter. So for example: for Product = "Clothes" & Color = "Blue", the YoY Change for Quarter = "2020 Q4" would be -0.25 (calculated using (6-8)/8). I would like this field to be calculated for each row of data. I attached the sample data in this post. The format of the Quarter field is especially tripping me up in regard to how to point to the quarter of the previous year for each Product & Color.

Any assistance with this is greatly appreciated! Thank you,

Paul

[apathetichell]

does this work?

I usually do some datetime magic - but in this case I just took the year as a number and subtracted 1.

[apathetichell]

update to show a ratio - instead of a raw number.

[atcodedog05]

Hi @paulwini 

Here is how you can do it.

Workflow:

1. Using sorting tool i am sorting by product & color on ascending so that each pair is grouped together. And sorting quarter by ascending so that its sorted as 2018 Q1, 2018 Q2....2019 Q1, 2019 Q2...

2. Using multi-row formula with grouby on product & color (so that YoY is calculated only for each product & color pair) I am calculating ([previous quarter:count]-[current quarter:count])/[previous quarter:count] (previous year is row-1 and currently quarter is row)

Hope this helps 🙂

[paulwini]

Thank you for this solution. This is what I'm looking for in regard to a field that shows the YoY change.

Is there a way to keep all original rows of data in the output? For example: I would like the output to still include 2018 quarter rows even though there is no 2017 data. I would just like the YoY change for the 2018 values to be NULL.

Additionally, let's say there was no 2019 Q4 data for Product = "Clothes"; Color = "Blue". Is there a way to keep the 2020 Q4 Product = "Clothes"; Color = "Blue" row in the data output and just show the YoY change as NULL?

Thanks again for all your help with this!

[atcodedog05]

I guess i misread the question and did Quarter over Quarter change 😅

[atcodedog05]

Hi @paulwini 

I have modified the workflow to fit the scenario.

1. Using formula tool extract year part & quarter part.

2. Using sort sort by product, color, quarter & year. So year is grouped up for each quarter.

3. Using groupby on Product, Color & year to get quarter change over year.

Hope this helps 🙂

[apathetichell]

I think mine did that - I have the year with no value as zero but I can change it to null() pretty easily.

[paulwini]

Thank you for the update! This wasn't in my original request, but is there a way to make sure a YoY Change value is NULL for a row of data that doesn't have a respective Count for the previous year&quarter?

For example: if there was no 2019 Q4 data for Product = "Clothes"; Color = "Blue" is there a way to show the YoY change as NULL for the 2020 Q4 Product = "Clothes"; Color = "Blue" row?

Thanks again.

[paulwini]

Sorry should have clarified: I would like the 2018 values to still be located within the original Product, Color, Quarter rows. So I would like there to still be 144 Records in the output.