Date format in Quarters

[jerry239]

Hello Everyone

I am trying to create a new field in Alteryx where I have a Date field which contains data in the format of MM/DD/YYYY. I have 5 years of data and I am trying to build a Gantt view in Alteryx. My requirement is to show my date fields like below:

For example, my data contains date range from 2022 to 2027. So what I need from my date field is that if my year is previous or next year then all the dates should combine/converted into Quarter 4 data and must not be filtered out and if my data is in current year and I should see all of my quarters. Below is an example:

I have 2022 Q1, 2022 Q2, 2022 Q3, 2022 Q4, 2023 Q1, 2023 Q2, 2023 Q3, 2023 Q4, 2024 Q1, 2024 Q2, 2024 Q3, 2024 Q4, 2025 Q1, 2025 Q2, 2025 Q3, 2025 Q4, 2026 Q1, 2026 Q2, 2026 Q3 2027 Q4, 2027 Q1, 2027 Q2, 2027 Q3, 2027 Q4.

Desired Output: -

2022 Q4, 2023 Q4, 2024 Q1, 2024 Q2, 2024 Q3, 2024 Q4, 2025 Q4, 2026 Q4, 2027 Q4.

I tried various methods in Tableau as well as Alteryx but no luck so far. Below is my approach using Alteryx:

If YEAR( [Date])< 2024
then YEAREND([Date])
elseif YEAR( [Date])>
2024
then MONTHEND(YEARSTART([Date]))
else [Milestone Start Date] ENDif

Can someone please help me with optimizing my query?

[binuacs]

@jerry239 One way of doing this

[jerry239]

Hello @binuacs 

thank you so much for looking into this and replying me but you converted date data type into a string. I am trying to build a Gantt view and this way it will not align my data with string. Could you please provide me a solution with Date data type?

[binuacs]

@jerry239 Alteryx only supports the ISO format (YYYY-MM-DD) as the Date type, the rest of the date format should be in string. Are you expecting the value 2023 Q4 in date format?

[jerry239]

@binuacs yes, I need these values in Date format. can there be a hack for the same?

[binuacs]

@jerry239 as I said before except for the ISO format(yyyy-mm-dd), Alteryx considers all the date formats in the string 

[jerry239]

@binuacs if you could please help me in designing this functionality in ISO format only which later on in Tableau I can change the format then that would be helpful. :)

[binuacs]

@jerry239 IF you are date in mm/dd/yyyy format then use the DateTimeParse() function to change in the ISO format

DateTimeParse([Date],'%m/%d/%Y')  - also change the type to Date in the formula tool

[jerry239]

@binuacs and how do I work on combining all year's data in last quarter months?

[binuacs]

@jerry239 you can use the formula that I provided in the workflow, note that the output will be in string format, If you are planning to make the changes in Tableau then use the equivalent Tableau formula at the Tableau level.