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

Calculating MTD, QTD, YTD from a date

Mario36
8 - Asteroid

Given a column of dates, how can I calculate if the date is a MTD / QTD or YTD ?

9 REPLIES 9
apathetichell
18 - Pollux

You need two columns of date - no?

 

For mtd datetimediff([date],[date2],"months")<1 (less than 1 month)

For qtd floor(datetimediff([date],[date2],"months")/3)<1 (less than 3 months)

for ytd datetimediff([date],[date2],"years")<1 (less than 1 year)

 

Mario36
8 - Asteroid

Do you mind sharing a workflow please. This is how I wish to see the data populate

DateMTDQTDYTD
1/31/2021  YTD
4/15/2021MTDQTDYTD
    
apathetichell
18 - Pollux

Are you aggregating financial data? or do you want date count? Ie the number of days that have passed since the first of the year?

 

 

Mario36
8 - Asteroid

So I want to know if the given date on a column is either a Month To Date, Quarter To Date or Year To Date. This solution would also be used to aggregate financial data, because I would be bucketing the data based on these time periods.

apathetichell
18 - Pollux

As discussed - here is a workflow which calculates the number of days from the start of the month, the start of the year and the start of the quarter...

Mario36
8 - Asteroid

I guess you've misunderstood my question. I would want to identify if a given date is a MTD, QTD or YTD.

Once Iam able to identify that, I will then proceed in grouping the dates based on these time periods. The screenshot below is how I wish to see my output.

Mario36_0-1618522879952.png

 

apathetichell
18 - Pollux

@Mario36Hi! It would have really really helped had you provided that data initially - or the other time I asked for clarification on what you were looking for.

 

It's fairly straight-forward change from the previous workflow - so here's the updated version. You can add in the static columns ("MTD",QTD") as you wish.

Mario36
8 - Asteroid

@apathetichell  Thank you !!
This is exactly what I expected. You are awesome.

apathetichell
18 - Pollux

thanks!

Labels