Hello,

I am trying to create a manual cross tab report to show sales data for each month of the year, rotating by whatever the current month is. For example:

Customer    June(Current)   May   April   March   Feb    Jan   Dec
Cust A          $$$                  $$$   $$$    $$$      $$$   $$$   $$$

The Fiscal Period field I am using is a string field of 2 characters. Here is what my formula is so far:

if (tonumber ({AR_CustomerSalesHistory.FiscalPeriod})) = (Month(CurrentDate)) then {AR_CustomerSalesHistory.DollarsSold}

For each month going backward I use:
if (tonumber ({AR_CustomerSalesHistory.FiscalPeriod})) = (Month(CurrentDate)-1) then {AR_CustomerSalesHistory.DollarsSold}

This works up until I get to December because it is trying to subtract 6 from 6 giving a value of 0 which doesn't equate to any month.

I have tried several other methods of getting the same data out of this but can't quite seem to get the fields to match. I have tried DateSerial, CDate, and DateAdd. Again I think the problem is that I am not able to relate the string field Fiscal Period to any of these formula return types.

Any help would be much appreciated!!

Jesse

This worked perfectly! Thanks so much for the help.

Jesse