Problem calculating dates from same field.

[geestrong]

Hello,

I am using CRXI on Oracle 8

Here is my problem. I am trying to calculate the difference between two dates from the same field. If a Rep has to return a plan back to a customer it is giving a code To Applicant(i.e. agency>APP or agency-CALL) and the date is entered. When a plan is returned from the applicant, then the following code is enter From Applicant(agency<APP).

Here is how the data looks:
BLC1 200402709 S-SWRC<APP 01/03/2005 0.00
BLC1 200402709 S-SWRC<APP
BLC1 200402709 S-SWRC>APP 12/29/2004 0.00
BLC1 200402709 S-SWRC>APP

I wrote two formulas @TO and @FROM
@TO
IF not isnull({PER_ACTION.ACN_CODE}) or {PER_ACTION.ACN_CODE}= "S-SWRC>APP"
then
{PER_ACTION.PER_ACN_COMPL_DATE}

@FROM
if not isnull({PER_ACTION.ACN_CODE}) or {PER_ACTION.ACN_CODE}="S-SWRC<APP"
then
{PER_ACTION.PER_ACN_COMPL_DATE}

then I have another formula called @CalAppTime which contains the following:
{@FROM}-{@TO}

The results I get is 0 and the answer should be 5 days in this case.

How can get these dates to calculate? Also, I will need to average the totals also

Thanks
Greg

 

[Madawc]

Group by BLC1 200402709. Do running totals, @From and @To, selecting the dates, maybe as minimum for FROM and maximum for TO. Then find the difference using a formula in the group footer.

I don't understand if not isnull({PER_ACTION.ACN_CODE}) or {PER_ACTION.ACN_CODE}="; if it is null then it cannot equal the value. If the field said "Bananas", then the test would pick a value, because "Bananas" is not null. Better if not isnull({PER_ACTION.ACN_CODE}) and {PER_ACTION.ACN_CODE}=". IN fact your existing code might work with that fix, though running totals are better.

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Madawc Williams (East Anglia, UK)

 

[geestrong]

Madawc

I am using the min and max solution... It looks like it is working so far. I am going to incorporate this logic in the report.

Geestrong