You are here: BI Publisher Calculate the Quarter of a given fiscal year in RTF template

Calculate the Quarter of a given fiscal year in RTF template

Written by  Anil
Rate this item
(1 Vote)

Using some arithmetic along with the mod() and floor() functions, you can calculate the quarter of a fiscal year based on a given date. The calculation we will use assumes that the fiscal year begins on the first of the month, and not some other day. We will first demonstrate this for a fiscal year that starts in January, and then show how to modify the formula for any given start month.


Sample XML 

<?xml version="1.0" encoding="iso-8859-1"?>

<List_Date>

   <G_Date>

     <Date>01/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>02/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>03/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>04/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>05/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>06/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>07/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>08/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>09/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>10/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>11/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>12/25/2001</Date>

   </G_Date>

</List_Date>

 RTF Design

Output:


Field Browser:

 

Adjust the formula for a different starting month Based on requirement

If you have a fiscal year with a starting month other than January, the formula is easily modified to account for this. You will note the highlighted part of the formula : floor(mod(((substr(Date,1,2) +11) div 3),4)) + 1. This 11 should be decremented for each successive month. So if your fiscal year begins in February, then you would replace it with 10. March would be 9, and so on until you reach 0 for December.
Once you've adjusted the formula to calculate the correct quarter, you need to update the string in the Calculated Value control to be "Feb-AprMay-JulAug-OctNov-Jan" for a fiscal year starting in February, and so on to "Dec-FebMar-MayJun-AugSep-Nov" for a fiscal year starting in December.

"If you found this article useful, please rate the same"

If you need any assistance in BI Publisher implementation or RTF Template design, we can

help you out. please send your query to   This e-mail address is being protected from spambots. You need JavaScript enabled to view it

Read 5618 times

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.