How do I convert my Date format to Quarter?

[4c9d83d4f87979b16d2b]

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.

[AMiller_Tri]

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]

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 · Accepted solution]

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]

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]

This is really useful. Thanks!

[AMiller_Tri]

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]

You're welcome, @David Westwood? !

[dk031]

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?