Formula Required Using Using Two Specific Sets of Dates

[beebass]

Hi there.

I'm currently using Crystal 9. I need to create a report that provides details of Customers Registered and Customers Ending the Service over two financial years (Financial year being Nov to Oct) by sales region and contract.

The difficulty I'm having is that each customer has a registered date which is when they began using our service and a Service End Date, which is when they finished. The sales report needs to provide details of the net gain/loss (number registered minus number finished) of customers for each month over the past 2 financial years - prefereably in a cross tab if possible. The problem I have is that the month should be across the top of the cross tab and I'm not sure which of the two dates to use or if a formula is required using current date or something like that.

I need or would like the cross tab to resemble something like the following:
Month
Sales Territory Contract Financial year November
1 A 05/06 25
06/07 36
Regards

Rob

 

[Madawc]

If you do an either-or formula field, that should be usable in your crosstab. Something like

if isnull({Service End Date}) 
then ToText({registered date}, "yy/MM")
else ToText({Service End Date}, "yy/MM")

If you want to show both sets of figures together, that's probably more than a crosstab can do. You could do it by a mock-crosstab. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then.

Crystal should have included an example along with the Crosstabs. You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.
Each running total will count the record if it was within the criteria - in your case, accounts within the two sets of dates.

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