Hello, I hope I'm not threadjacking, but it looks like the original poster has found resolution.

I've been watching this thread because I am trying to make a similar report. My report shows sales ({SalespersonHistory.CommissionDollarsSold}) by salesperson by fiscal period (i.e. month).

I would like to add/create a field with "% change" for each month from last year that same month to this year.

I believe your formulas and descriptions could work but I'm not sure how the example fields here line up with my fields since some are different data types.

So far I'm working with the fields:

*{SalespersonHistory.FiscalPeriod}* (a string field with the month as a number, 1-12=Jan-Dec)
*{SalespersonHistory.FiscalYear}* (another string field).
*{SalespersonHistory.CommissionDollarsSold} *(number field)

So far I am not working with a date field for each record like *{'Truck_Bookings_'.Truck Bookings Date}* in the example.

Would your instructions work in this case? If so, how do I need to alter it?

I tried making the 2009 formula as follows:

*if ({SalespersonHistory.FiscalYear})="2009" then {SalespersonHistory.CommissionDollarsSold} else 0*

I've made "@2010" similar to @2009 but with "2010" of course, and the "@monthly_difference" formula (which I call "@%_change") as follows:

*if Sum (**{@2009**}, {SalespersonHistory.FiscalPeriod})=0 then 100 else*

(Sum (*{@2010**}, {SalespersonHistory.FiscalPeriod})-Sum (**{@2009**}, {SalespersonHistory.FiscalPeriod})) / Sum (**{@2009**}, {SalespersonHistory.FiscalPeriod})*

I'm not using the @month formula because *{SalespersonHistory.FiscalPeriod} *already isolates the month. I don't get any errors from the formulas but the %_change fields in the report calculate only as either 100.00, or -1.00.

What am I missing?

Edited by atminnie - 10 Sep 2010 at 4:12am