calculcation field for dates

[Yuri24]

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?

[PhilipMannering]

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]

@Yuri24 
I agree with @PhilipMannering idea.

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

[Yuri24]

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

[Qiu]

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

[Yuri24]

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]

@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...