Calculating Calendar and Fiscal Quarters

Tuesday 9 January 2007 @ 2:41 pm

There are functions in Crystal Reports to extract most pieces of a date, including the Year, the Month and the Day. When I first posted this entry I wrote that there was no function for extracting the Calendar Quarter. Many thanks to Gordon Portanier of Crystalize in Canada for reminding me that the DatePart() function has a ‘q’ argument for quarters. So the formula for extracting the quarter would be:

Calender Quarter formula:

Totext ( DatePart ( 'q' , {Orders.Order Date}) , '0')
& 'Q'
& Totext (Year ({Orders.Order Date}), '0000')

To calculate Fiscal Quarters you use the formula below. Change the number next to the Q to reflect the appropriate quarter in your fiscal year. If your fiscal quarters don’t use the same 3 month periods as calendar quarters you may also need to change the number at the end of each IF statement. The numbers should reflect the number of the last month in each of your Fiscal Quarters. Regardless of which month numbers you assign to which quarter, make sure that the lowest month number you are going to use is the one that appears in the first IF line. The next lowest month number should be in the second IF line, etc. This example is for the federal fiscal year which starts in October:
Fiscal Quarter formula:

if Month ({Orders.Order Date}) <= 3
then "2Q" & Totext ({@Fiscal Year}, '0000')
else
if Month ({Orders.Order Date}) <= 6
then "3Q" & Totext ({@Fiscal Year}, '0000')
else
if Month ({Orders.Order Date}) <= 9
then "4Q" & Totext ({@Fiscal Year}, '0000')
else "1Q" & Totext ({@Fiscal Year}, '0000')

The formula called {@Fiscal Year} is described in another blog post.? To learn about the functions used in these formulas, and formulas in general, you should purchase my Expert’s Guide to Formulas.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro