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 Cloud Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Cloud.
SOLVED

How do I convert my Date format to Quarter?

4c9d83d4f87979b16d2b
6 - Meteoroid

For example, our current date is "05/18/2022" and I'm expecting my output to be "202202" which means the second quarter of 2022.

8 REPLIES 8
AMiller_Tri
Alteryx Alumni (Retired)

Hi @Chris Li? , thank you for your question!

Let's take this as an example:

First off, we can extract the year element by using the YEAR() function:

Then, the calculation to find out the quarter can be made by dividing the month element by 3, then using the CEILING() function on the output:

  • CEILING() - rounds the value up to the nearest integer.

 

Next up, we merge the 2 columns; Because you want the month to appear as 2 digits, we can use '0' as our delimiter:

 

I hope that helps; Please feel free to ask any additional questions =]

 

Thanks,

Amit.

4c9d83d4f87979b16d2b
6 - Meteoroid

Thank you so much, Amit. We can use this approach to get the correct output.

 

But going forward it would be perfect if we can have a quarter function like QUARTER() or we can convert data into "YYYYQQ" format.

 

Based on what I see, we will have a lot of Current VS last quarter/month/week logic in our use cases. So using one function/conversion can save us a lot of effort.

AMiller_Tri
Alteryx Alumni (Retired)

Hi @Chris Li? ,

While there isn't an out-of-the-box QUARTER() function, you can make one yourself via macros!

While there are multiple ways to calculate the quarter (E.G., via "conditional" and ask in what range the month() is); eventually, you'll need to recreate this step every time. That's time-consuming if you'll need to repeat that again and again.

 

See the documentation about macros here.

 

Regarding your scenario - if, after the steps I've shown, I remove the interim columns - the Macro created out of these 4 steps is very simple; an input column and an output column.

 

To create this Macro, first, select all 4 steps we've made, right-click and hit the "create a macro"

Next, you can name your Macro and give it a description. It will appear as any other transformation when you search for it!

 

Afterwards, you'll be presented with 2 tabs. To simplify, go over the 2nd tab, called "inputs".

There, rename the columns "input" and "output" accordingly; Hit save each time to save the changes made.

Then hit "create", and you'll be able to find your newly-created Macro either through the Macro button in the toolbar:

Or simply search for your specific Macro's name!

Inside you'll be able to insert the date column and the new column name, which will contain the quarter - as shown in the first picture in this post.

 

I hope this makes sense; feel free to ask any additional questions!

 

Thanks,

Amit.

4c9d83d4f87979b16d2b
6 - Meteoroid

Amit, thanks again, for introducing the Macro to me? I'm able to create a Quater Macro by myself. Is that possible to share my Quater Macro with other users?

TrifactaUsers
10 - Fireball

This is really useful. Thanks!

AMiller_Tri
Alteryx Alumni (Retired)

You're welcome, @Chris Li? .

Yes, you can share macros with other users. Here are 2 options:

  1. Share the flow. All relevant (used in the flow) Macros will be shared as well
  2. go to the Macros panel; export the macro, and send the exported Macro file to the other user
    1. The user can import the macro through the same Macros panel.

 

I hope that makes sense; Please feel free to ask any further questions =]

AMiller_Tri
Alteryx Alumni (Retired)

You're welcome, @David Westwood? !

dk031
5 - Atom

I am trying to use this formula but Ceil is not working.For example for January the first formula returns 0 and Ceil returns 0. Why is that?