Fiscal Quarters

[TEM3]

Using Crystal Reports 8.5/Oracle Tables......

I am using the Calendar1stQtr (2nd, 3ld, 4th) functions in a series of reports. However our quarters go by Fiscal Year. Calendar1stQrt 2004 would actually be 3ld Quarter 2005, etc.

That, in itself, is not a problem. But I want my title to indicate the correct Fiscal Quarter.

I am using this formula:

"Cumulative Cases/Assignments Received for 3ld Quarter " + totext(year(currentdate),0,"")

How can I add a 1 to the year so that I get 2005??

Or is there a better way to handle the whole thing???

 

[rosemaryl]

Dear TEM3,

Well, it wouldn't always be a 1, the common formula to determine fiscal year for July to June is:

Year(dateadd("M",6,Currentdate)) will always give you the correct fiscal year.

To get the correct fiscal month number you can do:

Month(dateadd("M",6,Currentdate))

and if you would like to print the correct Quarter names when grouping, for example, look at the help available under the Choose function ... which is very useful for this kind of thing.

To place the above in your title just wrap it in a totext:

Totext(Year(dateadd("M",6,Currentdate)),#)

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[TEM3]

rosemaryl:

I think I understand most of the above (and will play with your examples).

Another questions.... If, say, I am using Calendar1stQtr in a selection formula, is there a way to convert that function to an actual date range so that I can confirm I am looking at the dates I am interested in?

Of course I guess I could do a min/max summary on a date field in my data to check.......

 

[synapsevampire]

Consider using a Period table, which solves nuerous problems such as this.

I have a FAQ which includes SQL for SQL Server which you should be able to convert readily:

faq767-4532

This will allow you to join your data to this table and use this tables criteria (such as fiscal period) to quickly pull data, hence it's advantages in a data warehouse.

-k

 

[rosemaryl]

Dear Tem3,

If you have questions on my suggestion, let me know and I will try to give a better explanation.

To use the default quarter stuff in crystal, since your fiscal year isn't based on the calendar year, you have to modify to meet your fiscal calendar year.

First, create a parameter to prompt your users called Quarter. Populate the parameter defaults with, FirstQuarter, SecondQuarter, ThirdQuarter, FourthQuarter

I am assuming a Fiscal year of July to June. Next, create the following selection criteria formula:

//selection criteria
{Table.Date_or_DateTimeField}
in
switch
({?Quarter} = 'FirstQuarter',
dateadd("m",-6,minimum(Calendar1stQtr)) to 
dateadd("m",-6,maximum(Calendar1stQtr))+1,
{?Quarter} = 'SecondQuarter',
dateadd("m",-6,minimum(Calendar2ndQtr)) to 
dateadd("m",-6,maximum(Calendar2ndQtr))+2,
{?Quarter} = 'ThirdQuarter',
dateadd("m",-6,minimum(Calendar3rdQtr)) to 
dateadd("m",-6,maximum(Calendar3rdQtr))+2,
{?Quarter} = 'FourthQuarter',
dateadd("m",-6,minimum(Calendar4thQtr)) to 
dateadd("m",-6,maximum(Calendar4thQtr))+1
)

I did limited testing, but think this will work well.

Now, if you report on an entire fiscal year and want to group your data on the report by Quarter, then create this formula and group the records on it.

//{@Quarters} formula
Select cdate({Table.DateTimeField})

case 
dateadd("m",-6,minimum(Calendar1stQtr)) to 
dateadd("m",-6,maximum(Calendar1stQtr)) : 'First Quarter'
case
dateadd("m",-6,minimum(Calendar2ndQtr)) to 
dateadd("m",-6,maximum(Calendar2ndQtr))+ 1 : 'Second Quarter'
case
dateadd("m",-6,minimum(Calendar3rdQtr)) to 
dateadd("m",-6,maximum(Calendar3rdQtr)) +1 : 'Third Quarter'
case
dateadd("m",-6,minimum(Calendar4thQtr)) to 
dateadd("m",-6,maximum(Calendar4thQtr))  : 'Fourth Quarter'

I suggest you use specified grouping on the above to place them in fiscal quarter order.

And, finally, if you need to print the date range for the Quarters formula it would be as follows:

//{@QuartersDateRange}
Select {@Quarters}
case 'First Quarter' :
totext(dateadd("m",-6,minimum(Calendar1stQtr))) & ' to ' &
totext(dateadd("m",-6,maximum(Calendar1stQtr)))  
case'Second Quarter':
totext(dateadd("m",-6,minimum(Calendar2ndQtr))) & ' to '&
totext(dateadd("m",-6,maximum(Calendar2ndQtr))+ 1) 
case
'Third Quarter' :
Totext(dateadd("m",-6,minimum(Calendar3rdQtr))) & ' to ' &
Totext(dateadd("m",-6,maximum(Calendar3rdQtr)) +1) 
case
'Fourth Quarter' :
Totext(dateadd("m",-6,minimum(Calendar4thQtr))) & ' to ' &
Totext(dateadd("m",-6,maximum(Calendar4thQtr)) )

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762