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

Sanford Whiteman -

At one of my clients, sales data is regularly uploaded into Marketo custom objects. A “your recent activity” email goes out to customers a few days after the quarter ends (could be a few weekdays into 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 fancy fiscal quarters, just standard calendar quarters) ends on the last day of a month: March, June, September, December.

(For now, bookmark the fact that we’ll need to know the last day of an arbitrary month in an arbitrary year. Obviously — *shudder* — 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 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 be the current month
  • the quarter end month could be in the future

Thinking mathematically, 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:

Month 2: nope, not greater than or equal to 4
Month 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:

Month 2: nope, not >= 8
Month 5: nope, not >=8
Month 8: yep, that’s equal to 8, so the current quarter ends at the end of this very month (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 (you could create a new Calendar from scratch, but cloning preserves the timezone/locale)
  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 $isQuarterEndToday if today happens to be the exact end of the quarter (you might be interested in that special case)

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?