Fiscal year formula

[Former Member]

Crystal 11, I understand grouping dates via traditional calendar ranges but how do you create a formula that groups 10 years of monthly data into our Fiscal year grouping which is September 1 to August 31?

Thanks

[Former Member · Accepted solution]

You can create 2 formulas that extracts the month and year from the date field.

month( {SuperCheck.CkDate})

(year({SuperCheck.CkDate})-10.00) -- this will return your year 10 years prior

create a group formula

if datefield in month and year then true

something like that will work.

you may want to create parameters to feed the dates to run at any time.

[Former Member · Accepted solution]

Will this group each fiscal year seperately? so the months in each fiscal group will be september 1999 to august 2000 and the next group will be for months september 2000 to august 2001 and so on??

[Former Member · Accepted solution]

if you create a year(field) formula

than create a group from that iit should group by the year.

you will need to try it, the data im working with i cant test it for you

[Former Member · Accepted solution]

Sorry for being so elementary here.

I have this date field {Electrical_.Begin_Date} that actually goes back to 1997.

Created one formula called Month: Month({Electrical_.Begin_Date})

Created another formula called Year: (year({Electrical_.Begin_Date})-13.00)

I went to design view and change group and check marked use Formula as Group Sort Order and

created Group Formula: (year({Electrical_.Begin_Date})-13.00) and receive an error "A Constant Expression is required here" with the Electrical_Begin_Date highlighted.

My report group is on the Electrical_Begin_Date field.

Tried to create another Group Formula: if {Electrical_.Begin_Date} in {@Month} and {@Year} then true

and receive an error "A Date-Time is required here with the @Month highlighted.