Dynamic Quarterly YoY comparison

[pokhan27]

Hi Expert

I have trying to have dynamic Quarterly YoY comparison . scenario is given below

for example: 

id	region	20-Feb	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-20
1	xyz	11000	15000	15000	23000	12000	9000	11000	15000	11000	12000	13000	14000

in above case i am doing qtd comparison. if i am in may 2021 and doing Quarterly YoY comparison i want to have the value as "Q1-2020+Q2-2020+May2020" i don't want entire q3 data. similarly i want to make this comparison dynamically like on june it will be "Q1-2020+Q2-2020+May2020+June2020" and in july it will be "Q1-2020+Q2-2020+Q3-2020"  and so on. can we do that in alteryx. 

[Jean-Balteryx]

Hi @pokhan27 ,

To me it looks like you need a Running total. I made it for each year after transposing the data.

Also it will be dynamic if you have new months.

[pokhan27]

no. 

from above data it would have qtry total as 

id	region	Q2 -2020	Q3-2020	Q2-2021	Q3-2021
1	xyz	41000	44000	37000	38000

now suppose i am in May 2021 and doing monthly "Quarterly YoY Calculation"  than Q3-2021 the value would be  Q1+Q2+May20 since we are not in June 2021 we do not include the data of Q3 2020(June and July value)

i am doing the monthly data analysis. so i need same period comparison (parallel) 

or my desire out would be 

Assume	Q1-2021=85000	Q1-2020=43000
ID	Region	Q1-2021	Q2-2021	May-20	Combined 2020  (Q1 + Q2 + May-20)
1	xyz	85000	37000	23000	107000

[apathetichell]

your problem is that you are doing date function strategies with non-dates... You need to transpose this, convert your column names to real dates and then apply grouping strategies and then a summarize...

so the short answer is - yes we can do this in Alteryx. don't know if I have time to help you - but post some data in .xlsx and probably someone else can...

[pokhan27]

Sample Data

[Christina_H]

When does your year start?

This will give you calendar year total to date compared to previous calendar years total to the same month, but that might not be what you're after.  If you also want quarterly subtotals you would need to identify which quarter each month is in.

[mbarone]

As others have said, you'll need to familiarize yourself with the Transpose tool, and then use string-to-date functions (expand Date to see all the functions/formats).  It's not  trivial, but is very possible.

[atcodedog05]

Hi @pokhan27,

Here is my take.

Workflow:

1. Using transpose to convert all columns to rows.

2. Using formula tool to create date, year and month.

3. Using summarize to find max month date and month number. 

4. Using filter tool to keep only months till max month.

5. Using formula to create year-quarter.

6. Using transpose tool to create Quarterly and Yearly total.

7. Using join tool to join them together.

Hope this helps : )

[pokhan27]

my fiscal year is nov-oct and we are doing monthly reporting but quaterly YoY comparison

[pokhan27]

i did reach to this point, 

my issue is: 

I am doing same period comparision. 

lets say i am doing for May 2021 reporting. 

than i am comparing data with "Q1-2020+Q2-2020+May 2020" value with todays value. which is Q12021+Q22021+May2021