topic Re: Quarter formula in Workato Pros Discussion Board

Quarter formula

nvinay091 — Thu, 10 Feb 2022 21:08:44 GMT

How to get which quarter from the date format 2022-01-31
Is there any formula to get, quarters ( Q1, Q2, Q3,Q4) from the date

Re: Quarter formula

jblanchett — Thu, 10 Feb 2022 21:48:01 GMT

I never found a great way to do this. Best options I found were using strftime to get month from date then using a hash mapping to get quarter (then append year). Lookup table is an option as well. If you are working with a system that has a table of fiscal quarters already with start/end dates, you can search that system with date parameters (e.g. NetSuite).

Re: Quarter formula

kale-bogdanovs — Thu, 10 Feb 2022 22:04:28 GMT

Hey there!

One way to do this would be to create a lookup table mapping months to quarters. This works even if you have a non-standard financial year. Here's an example table:

You can then use the lookup table action to get the quarter from a date.

Note that to do this, i've used formulas to:

* change the date to a string format

* split it by the "-" character to give a list that will look like ["2021", "04", "12"]

* selected the second element of the list, which will be the month.

The returned quarter value is will now be available in the recipe data widget and I can map it wherever I need.

Hope this helps.

Re: Quarter formula

jonathan-wood — Thu, 10 Feb 2022 22:07:06 GMT

You could also divide the month by 3 and add 1 instead of using a map or lookup table. I think you'd have to convert the output from strftime to an integer with to_i and then convert back, so the formula may get complicated, but could save building a lookup.

Re: Quarter formula

jeff-allen — Thu, 10 Feb 2022 23:57:12 GMT

You can consider using a Formula that uses conditions based on the Month value of the date. It is possible to string Ternary logic together to get the quarter output from a formula.

Re: Quarter formula

vinny-sosa — Tue, 15 Feb 2022 03:19:25 GMT

Not that I am aware of. Though you may consider using a function to calculate it. Use logger to test and replace "date" with whatever variable you want to use to determine the quarter. You may use now for that as well... see below. If your quarters line up differently to months, edit the below however you see fit.

date.strftime("%mm").match?(01|02|03) ? "q1" :
date.strftime("%mm").match?(04|05|06) ? "q2" :
date.strftime("%mm").match?(07|08|09) ? "q3" :
date.strftime("%mm").match?(10|11|12) ? "q4" :
""

Re: Quarter formula

max-knutsson — Tue, 15 Feb 2022 15:32:30 GMT

Looks like the quarter is not a Ruby default method, so the way we can do this is use one of the code connectors like JavaScript. Here is an example how it will look like. Given the input_date is the date you want to calculate quarter for (or you can just type today in formula. We return quarter as output.

1. add JavaScript connector

2. Create input called input_date with type date

3. create output quarter of type integer

4. add this code snippet:

exports.main = ({input_date}) => {

var today = new Date(input_date)

var quarter = Math.floor((today.getMonth() + 3) / 3);

return { quarter };

}