Free Trial

Alteryx Designer Desktop Discussions

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

calculcation field for dates

Yuri24
8 - Asteroid

Hello,

 

I am currently having date field that is monday of every week for the past 1 yr. Considering beginning of current year as 01-oct-2022 and end of current year as 30-sep-2023 (performance yr), how to create a CF for YTD, QTD, MTD, WTD and also Prioryr YTD, Prior yr MTD, Prior yr QTD, Prior Yr WTD?

6 REPLIES 6
PhilipMannering
16 - Nebula
16 - Nebula

I would create fields for Year, Quarters, Months and Weeks (The Specifiers here should help: https://help.alteryx.com/20231/designer/datetime-functions) and then use the Running Total Tool Grouped by these newly created fields.

 

The same applies to your PY fields except you're doing this now for 01-oct-2021 to 30-sep-2022.

Qiu
21 - Polaris
21 - Polaris

@Yuri24 
I agree with @PhilipMannering idea.

If you have some sample data and desired output, we can take a closer try.

Yuri24
8 - Asteroid

@Qiu Here is the dataset with dates for which I am looking to create a  CF for YTD, QTD, MTD, WTD

Qiu
21 - Polaris
21 - Polaris

@Yuri24 
I am sorry but what will be desired output look like?

Yuri24
8 - Asteroid

Sorry, so

if dates between 01-oct-2021 and 01-sep-2022 then 'priorYrYtd'

elseif dates between 01-oct-2022 and 01-sep-2023 then 'Ytd'

elseif dates between 01-jul-2022 and 01-sep-2022 then 'PriorYrQtd'

elseif dates between 01-jul-2023 and 01-sep-2023 then 'Qtd'

elseif dates between 01-jul-2022 and 01-aug-2022 then 'PriorYrMtd'

elseif dates between 01-jul-2023 and 01-aug-2023 then 'Mtd'

elseif dates between 03-jul-2023 and 09-Jul-2023 then 'Wtd'

elseif dates between 04-jul-2022 and 10-jul-2022 then 'PriorYrWtd'

 

this is currently hardcoded but it has to be dynamic

 

Thanks

Qiu
21 - Polaris
21 - Polaris

@Yuri24 
Thank you for the logic.
I will just take an example of MTD to demonstrate how we can play with DateTime Function basically.

Here is another sample.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/MTD-and-YTD-based-on-today-s-d...

0706-Yuri24.PNG

Labels
Top Solution Authors