Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic Quarterly YoY comparison

pokhan27
8 - Asteroid

Hi Expert

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

 

for example: 

idregion20-FebMar-20Apr-20May-20Jun-20Jul-20Feb-21Mar-21Apr-21May-21Jun-21Jul-20
1xyz11000150001500023000120009000110001500011000120001300014000


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. 

 

20 REPLIES 20
Jean-Balteryx
16 - Nebula
16 - Nebula

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
8 - Asteroid

no. 

 

from above data it would have qtry total as 

idregionQ2 -2020Q3-2020Q2-2021Q3-2021
1xyz41000440003700038000

 

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=85000Q1-2020=43000    
     
ID RegionQ1-2021Q2-2021May-20Combined 2020  (Q1 + Q2 + May-20) 
1xyz850003700023000107000 
apathetichell
18 - Pollux

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
8 - Asteroid

Sample Data

Christina_H
14 - Magnetar

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
16 - Nebula
16 - Nebula

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
22 - Nova
22 - Nova

Hi @pokhan27,

 

Here is my take.

 

Workflow:

atcodedog05_0-1627491246325.png

 

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
8 - Asteroid

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

pokhan27
8 - Asteroid

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

Labels