YOY or Period over Period Comparison without date field 1

[HollyS]

Using Crystal 10 - MAS90/200 database

I'm trying to write a report that will analyze broker sales. They want to review the sales multiple ways, either YTD over YTD or QTD over QTD, but the database that I'm pulling from doesn't have a specific date field. The fields I have to choose from are Fiscal Year, Fiscal Period, Dollars Sold.

The ideal end result would look like this:

Broker1
Customer1 CustomerAddress FY12YTDSales FY11YTDSales DIFF
Customer2...

or

Broker1
Customer1 CustomerAddress FY12QTDSales FY12QTDSales DIFF
Customer2...

Is this possible give the fields that I have available?

Thanks,
Holly

 

[briangriffin]

The best way would be to create a date table or view. It makes writing the report easier and it's reusable.

It would look something like this - you'll probably add additional fields to address different reporting needs.

Date Fiscal Year Fiscal Month FiscalQtr
1/1/2012 2012 7 3
2/1/2012 2012 8 3
3/1/2012 2012 9 3
Etc. etc.

For this report you would only need to populate it with the first day of every month, but most of the time you would insert a record for every day for the next ten years or so.

Then join the table/view on fiscal year and fiscal period to give you a date.