Find the end of the current quarter and/or previous quarter in Velocity

[SanfordWhiteman]

A client gets regular uploads of quarterly sales data into Marketo custom objects. They send a “your recent activity” email to customers when each quarter ends or a few days after that (could be the first or second weekday of the next month).

The email must include the exact quarter end date: “These are your purchases for the quarter ending June 30, 2022.”

Obviously this is a job for Velocity, with its robust ability to calculate dates and times. But there isn’t a built-in method like getQuarterEndDate() — we have to build it ourselves from date and time methods. So let’s dive in.

Start with the includes

As always, we start with the standard includes from the seminal post on days and times in Velocity:

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $ISO8601DateTime = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateTimeWithSpace = "yyyy-MM-dd HH:mm:ss" )
#set( $ISO8601DateTimeWithMillisUTC = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" )
#set( $ISO8601DateTimeWithMillisTZ = "yyyy-MM-dd'T'HH:mm:ss.SSSZ" )

Think it through

Let’s think about quarters. Each quarter (we’re not using any fiscal quarters, just calendar quarters) ends on the last day of a month: March, June, September, December.

(For now, bookmark the fact that we need to know the last day of an arbitrary month in an arbitrary year. Obviously — well, to me! — we aren’t going to create a list of all the possible lengths of months in any year, remembering leap years are different. We’re gonna leverage Java’s inherent knowledge of past and future calendars.)

Velocity & Java don’t natively understand business quarters. So we’ll create our own list of the quarter ending months. In Java, months are zero-based (0-11), so we could bluntly use numbers:

#set( $quarterEndingMonths = [2,5,8,11] )

But though it’s accurate, that’s needlessly obscure. We declared $calConst, so let’s use those constants instead:

#set( $quarterEndingMonths = [$calConst.MARCH,$calConst.JUNE,$calConst.SEPTEMBER,$calConst.DECEMBER] )

Easier to read, right?

Now consider the possible relationships between today and the month the current quarter ends:

• the quarter end month could also be the current month
• the quarter end month could be in the future

Thinking mathematically, those mean the current quarter-ending month is:

• the first quarter-ending month whose 0-based index is greater than or equal to the 0-based index of the current month
  

Say the current month is 4 (May, not April!). We iterate over the possible ending months until there’s a match:

• 2: nope, not greater than or equal to 4
• 5: yep, greater than or equal to 4, so the current quarter ends at the end of month 5 (June)

Now say the current month is 8 (September). Finding a match:

• 2: nope, not >= 8
• 5: nope, not >=8
• 8: yep, that’s equal to 8, so the current quarter ends at the end of this very month — a special case that might be interesting in its own right, but either way the quarter ends at the end of September

 

Getting the last day of a month

Once we know the month, we need the last day of that month. Again we’re not building crazy lookup tables in Velocity. Java knows that stuff, we just need to know how to tap it. The Calendar.getActualMaximum() method lets us find the largest possible value of any part of a date given the other parts. So it’ll tell us the largest Calendar.DATE (i.e. day) value you could set in a given month, even accounting for leap years. Think of it as how far you could spin the dial on a calendar picker.

 

Implementing the logic

So how do we code the above? Like so:

#set( $currentMonth = $calNow.get($calConst.MONTH) )
#set( $calCurrentQuarterEnd = $calNow.clone() )
#set( $quarterEndingMonths = [$calConst.MARCH,$calConst.JUNE,$calConst.SEPTEMBER,$calConst.DECEMBER] )
#foreach( $endMonth in $quarterEndingMonths )
  #if( $endMonth >= $currentMonth )
    #set( $ret = $calCurrentQuarterEnd.add( $calConst.MONTH, $math.sub($endMonth, $currentMonth) ) )
    #set( $ret = $calCurrentQuarterEnd.set( $calConst.DATE, $calCurrentQuarterEnd.getActualMaximum($calConst.DATE) ) )
    #set( $isQuarterEndToday = $date.whenIs( $calNow,$calCurrentQuarterEnd ).getDays().intValue().equals(0) )
    #break
  #end
#end

To review:

1. clone() the current Calendar object to a new quarter end Calendar (cloning is easier than creating a new Calendar from scratch, as it preserves the time zone)
2. loop over the possible ending months
3. when we find a match
   1. set the quarter end Calendar’s month to the matched month
   2. set the quarter end Calendar’s day to the last day of that month
   3. also set a boolean if today happens to be the exact end of the quarter

That gives us $calCurrentQuarterEnd, which we can format to our liking:

## format as "June 30, 2022"
${date.format("MMMM d, yyyy",$calCurrentQuarterEnd)}

Getting the previous quarter end given the current

What if we wanted to know when the previous quarter ended?

Easy: clone $calCurrentQuarterEnd to a new Calendar, spin its month back exactly 3 months, then spin the day to the last day:

#set( $calPreviousQuarterEnd = $calCurrentQuarterEnd.clone() )
#set( $ret = $calPreviousQuarterEnd.add( $calConst.MONTH, -3 ) )
#set( $ret = $calPreviousQuarterEnd.set( $calConst.DATE, $calPreviousQuarterEnd.getActualMaximum($calConst.DATE) ) )

Food for thought

When you’ve got some time, experiment with more day/time tricks. For example, what if someone used fiscal quarters that spanned years (like Dec-Feb, Mar-May, Jun-Aug, Sep-Nov) — how would you implement that?