Bar Graph - Group by Custom Fiscal Year Formula 1

[dsmbwoy]

Hi Everyone,

I have a bar graph chart in my Crystal Report that groups data by year (Jan - December) but what I need is to do is change the fiscal year to (April 1 - March 31). Can someone please help me generate the formula required to do this.


Thanks,

DB

 

[lbass]

Use the following, assuming that you call the fiscal year by the ending year:

//{@fiscalyear}:
year({table.date}+275)

-LB

 

[dsmbwoy]

Hi lbass. Sorry I am not too clear on how to implement your solution. Do I plug your formula into the following section? Chart Expert --> Data Tab --> Click on the 'Order' button below the 'On change of' drop-down list --> Check the 'Use a Formula as Group Sort Order--> paste your formula into the formula editor?


Thanks,

DB

 

[lbass]

I thought you wanted the chart to be based on fiscal year, in which case you would use the formula I suggested as your "on change of" field.

-LB

 

[dsmbwoy]

I currently have my chart configured to display yearly data in a bar graph by grouping the data by year (plug in date field in 'on change of field' --> click on the date field and click 'Order' --> select 'for each year' on the bottom drop down list.

So my bar graph has bars representing the following.

.
.
.
-2007 data (Jan 1 - Dec 31)
-2008 data (Jan 1 - Dec 31)
-2009 data (Jan 1 - Dec 31)

I would like to modify the start and end date parameters of the yearly grouping from the default (Jan 1 - Dec 31) to (April 1 - March 31).


Thanks,

DB

 

[lbass]

All you need to do is remove your current date field from the chart and replace it with the fiscal year formula. What problem are you running into when you do this? If you want it to SAY fiscal year, then change the formula to:

"FY "+totext(year({table.date}+275),0,"")

-LB

 

[dsmbwoy]

LBASS you are a geneous! Thank you very much for your help. Just curious however, I understand that year({table.date} represents the year of the {table.date} field but I don't quite understand the meaning of adding '+275' to the end. If I wanted to start the fiscal year start date to March vs April for example how would that formula look?


Thanks,

DB

 

[lbass]

275 is the difference in days between April 1 and January 1 of the following year. For other fiscal years, use a formula like this (using March 1):

datediff("d",date(2009,3,1),date(2010,1,1))

This wouldn't work correctly if the month was February, however, for leap years.

-LB

 

[dsmbwoy]

Thanks once again LBASS!