DataWeave 2 - Finding Quarter of the Year

February, 22 2021
Manik Magar
mule
dataweave2

We often do date manipulations in DataWeave. What if we need to find quarter of the year for a given date? What if quarters start at February? How to do it in DataWeave? Let’s explore it in this post.

When finding the calendar quarter of the year, we can use standard date formatting pattern.

Table 1. Standard Quarters
Script	Output (application/csv)
%dw 2.0 output application/csv var monthArray:Array = 1 to 12 map ('2020-$($)-01' as Date {format: "uuuu-M-dd"}) --- monthArray map { month: $ as String {format: "MMM"}, quarter: $ as String {format: "q"} (1) }	month,quarter Jan,1 Feb,1 Mar,1 Apr,2 May,2 Jun,2 Jul,3 Aug,3 Sep,3 Oct,4 Nov,4 Dec,4

Table 1. Standard Quarters

Script

Output (application/csv)

%dw 2.0
output application/csv
var monthArray:Array =
                    1 to 12 map ('2020-$($)-01' as Date {format: "uuuu-M-dd"})
---
monthArray map {
    month: $ as String {format: "MMM"},
    quarter: $ as String {format: "q"} (1)
}

month,quarter
Jan,1
Feb,1
Mar,1
Apr,2
May,2
Jun,2
Jul,3
Aug,3
Sep,3
Oct,4
Nov,4
Dec,4

1	Use standard date format pattern `q` for getting calendar quarter of the year.

Some organizations follow February to January fiscal year cycle. First quarter starts from February instead of January.

In this case, the standard q formatted date won’t give correct quarter. We can create a simple custom function that can do this for us.

Table 2. Feb to Jan Quarters
Script	Output (application/csv)
%dw 2.0 output application/csv var currentMonth = now() as String {format: "M"} as Number fun getQuarter(date: Date): Number = do { (1) var currentMonth = date as String {format: "M"} as Number var quarter = (( currentMonth / 3) as String {format: "#"}) as Number --- if (quarter == 0) 4 else quarter } var monthArray:Array = 1 to 12 map ('2020-$($)-01' as Date {format: "uuuu-M-dd"}) --- monthArray map { month: $ as String {format: "MMM"}, quarter: getQuarter($) }	month,quarter Jan,4 Feb,1 Mar,1 Apr,1 May,2 Jun,2 Jul,2 Aug,3 Sep,3 Oct,3 Nov,4 Dec,4

Table 2. Feb to Jan Quarters

Script

Output (application/csv)

%dw 2.0
output application/csv
var currentMonth = now() as String {format: "M"} as Number

fun getQuarter(date: Date): Number = do { (1)
    var currentMonth = date as String {format: "M"} as Number
    var quarter = (( currentMonth / 3) as String {format: "#"}) as Number
    ---
    if (quarter == 0) 4 else quarter
}

var monthArray:Array =
                    1 to 12 map ('2020-$($)-01' as Date {format: "uuuu-M-dd"})
---
monthArray map {
    month: $ as String {format: "MMM"},
    quarter: getQuarter($)
}

month,quarter
Jan,4
Feb,1
Mar,1
Apr,1
May,2
Jun,2
Jul,2
Aug,3
Sep,3
Oct,3
Nov,4
Dec,4

1	Custom function to calculate quarter of the year.

How does this work?

Function gets the current month and divides it by 3.
Result is formatted to an integer which results in rounding to nearest integer number.
All first months of each calendar quarter divisions being x.33 gets rounded to lower number. Eg. January → 0.33 → 0, April → 1.33 → 1 etc.
All new quarters except for January are returned as-is. January (counted to 0) is remapped to 4th quarter.

That’s all for this short post. Hopefully, you learned something new here. For more explorations, you may also see other posts related to DataWeave 2.

If you have any thoughts or feedback on this article, feel free to comment on this article or find me on Twitter or on manik.magar.me.

Follow @manikmagar on twitter to get updates on new posts.

DataWeave 2 - Finding Quarter of the Year

Stay updated!