Formula to compare sales data from 2 financial years 1

[beebass]

HI

I'm currently using crytal 9 and have been asked to design a report that will display a table and corresponding graph that shows sales data for a particular region for from the current and previous financial years (the financial year is 1st Nov to 31st Oct). The set up of the table should be that it gives the month name as the header row and the financial year as the header column:
Nov Dec Jan Feb Mar Apr May Jun Jul etc
Financial Yr 06/07 ££ ££ ££ ££ ££ ££ ££ ££ ££
Financial Yr 05/06 ££ ££ ££ ££ ££ ££ ££ ££ ££

Do you know of a formula that will filter out the relevant sales figures from the financial years so that the report is set up to roll over to the next financial year automatically and still pick up the relevant figures from the previous financial year.

Would I then need to put the figures in to a crosstab?

Thanks

BB

 

[lbass]

You could use a selection formula with a parameter for fiscal year. The following assumes your fiscal year is identified by the ending year:

{table.date} in date({?fiscal year}-2,11,1) to date({?fiscal year}, 10,31)

You could then insert a crosstab that uses a formula for the row:

if {table.date} in date({?fiscal year}-2,11,1) to date({?fiscal year}-1, 10,31) then
"Financial Year "+right(totext({?fiscal year}-2,0,""),2)+"/"+right(totext({?fiscal year}-1,0,""),2) else
if {table.date} in date({?fiscal year}-1,11,1) to date({?fiscal year}, 10,31) then
"Financial Year "+right(totext({?fiscal year}-1,0,""),2)+"/"+right(totext({?fiscal year},0,""),2)

For your column field, use a formula:

month({table.date})

In the crosstab, select this column field->group options->options->customize group name->x+2 and enter:
monthname(month({table.date}),true)

-LB