[b]Problem summing sales based on hire date[b] 1

[sskeen]

Using Crystal Reports XI R2 reading DB2 database.

I am working on a formula that takes a person's hire date and compares it to the end date of a date range parameter. I have two columns that I need to Sum based on this. The first column needs to sum their sales based on the first 12 months from their hire date and the second column needs to sum their sales based on their 13-24 months hired. I've tried using a DateDiff to get the number of months contracted and then summing it and also tried the DateAdd. It gives me a sum of all months contracted. Thanks for any suggestions.

 

[lbass]

Instead of describing this, please show us the formulas you have tried along with some sample data and expected results.

-LB

 

[Madawc]

You could do pair of running totals, using the number of months found by DateDiff. (Assuming that comes out correctly - show it on a temporary display line if you are unsure.)

The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[sskeen]

Sample Sales 1st 12 Sales 13-24
Name Hired mths hired mths hired
Johnson 8/1/2008 16,000 885
Smith 7/28/2008 50,000 2,000
Brown 1/1/2009 8,000

A sample is listed above with the names and their hire dates. On my table I have an activity date which is the date they sold a product on and their sales for that date. I check a date range parameter (DateRange) against their activity date to sum on. I need to sum their sales for the first 12 months they were hired in one column and the next column their 13-24 months hired like it shows in the example above. I have a grouping for the id number that is assign to the names above. I tried a running total with evaluating and resetting it based on their id number, but I also need to check the number of months hired. Since that won't work, I setup a formula on the group header with:
WhilePrintingRecords;
NumberVar Sales1st12;
Sales1st12 := 0;

Then I set the detail up with this:
WhilePrintingRecords;
NumberVar Sales1st12 = 0;
if {@Mos_Contracted} >= 0 and {@Mos_Contracted} <= 12 and
({table.ActivityDate} >= cdate(year(currentdate)-1,1,1) and {table.ActivityDate} in {?DateRange})
then
Sales1st12 := Sales1st12 + {table.Sales}

And the footer with this:
WhilePrintingRecords;
NumberVar Sales1st12;
Sales1st12;

I'm doing the same thing for the 13-24 month with the exception of changing the Mos_Contracted to 13-24. The Mos_Contracted formula is:
DateDiff ("m",{table.HireDate},{table.ActivityDate})
I've also tried using DateAdd in the formula at the top with no luck.
dateadd("m", 12, {table.HireDate}) >= {table.ActivityDate}

Thanks for any suggestions

 

[lbass]

I think your Mos_Contracted formula should be based on currentdate (or possibly related to the selected date range), not activity date.

-LB

 

[sskeen]

I changed that and it's bringing back the correct number for the Mos_Contracted, but not summing the two separate columns correctly for the first 12 months and second 12 months.

 

[lbass]

Rethinking this, I think you should instead use formulas like this:

//{@1st12}:
WhilePrintingRecords;
NumberVar Sales1st12;
if {table.ActivityDate} in {table.hiredate} to dateadd("m", 12, {table.hiredate})-1 then
Sales1st12 := Sales1st12 + {table.Sales}

//{@2nd12}:
WhilePrintingRecords;
NumberVar Sales2nd12;
if {table.ActivityDate} in dateadd("m", 12, {table.hiredate}) to dateadd("m", 24, {table.hiredate})-1 then
Sales2nd12 := Sales2nd12 + {table.Sales};

I'm not sure what the daterange parameter is supposed to limit, but you should use that in your record selection formula. If you are selecting people who were hired within a certain daterange, then use:

{table.hiredate} = {?DateRange}

...in your selection formula.

-LB

 

[sskeen]

Thanks LB. This works great!