Comparing dates by using other criteria

[ginaburg]

I am using Crystal 11
I am trying to get the difference between dates based on the approver of an invoice.
Approver 1 approves invoice on 12/3/14
Approver 2 approves invoice on 12/6/14
Approver 3 approves invoice on 12/15/14
I need to get the number of days between approvals.
My fields are ApprovalSeq (approver number) DateApproved

I need to see it like this
Approver 1 12/3/14
Approver 2 12/6/14 3 days
Approver 3 12/15/14 9 days

Any help would be appreciated.

Gina

 

[briangriffin]

If the records are either sorted or grouped by invoice number, then by date of approval, then you can use the previous function to compare the dates of two records:

if {InvoiceNumber} = previous({InvoiceNumber}) then
totext(datediff("d",previous({ApprovalDate}),{ApprovalDate}),"#",0) + " days"
else ""

 

[BettyJ]

Create a formula,

@DateDifference
whileprintingrecords;
{DateApproved}-previous({DateApproved})& " days";

 

[ginaburg]

The records are sorted by invoice number but where does the approver come into the formula.

I need to see that
Group 1 - Invoice 123
approver 1 approved on 12/3/14
approver 2 approved on 12/6/14
approver 3 approved on 12/15/14 with the number of days between each one.

 

[briangriffin]

It seems that you would need to sort by approval date if each invoice has multiple approvers. If you sort/group by approver then the dates won't be sequential and the formula won't work. Please post some sample detail data, and the results of the formula so we can see where it's breaking down.

 

[SkipVought]

The Approver name seems to be irrelevant. You seem to be looking for the days between approvals, without regard to exactly who it was doing the approving.

 

[ginaburg]

After putting in a grouping for approver, both of your suggestions worked. Thank you.

Here is what I have for the date difference between approvers

if {APUI.APRef} = previous({APUI.APRef}) then
totext(datediff("d",previous({APUR.DateApproved}),{APUR.DateApproved}),"#",0) + " days"
else ""

Now I need to get the date difference for the first approver between invoice date and approval date.
So I tried this. I get the date difference from invoice to approver for all approvers except the first one. It come up blank.

if {APUI.APRef} = previous({APUI.APRef}) then
totext(datediff("d",({APUI.InvDate}),{APUR.DateApproved}),"#",0) + " days"
else ""

What am i doing wrong?

 

[briangriffin]

I think I'd try to simplify things first. Put the datediff in the details section:

datediff("d",",{APUI.InvDate}),{APUR.DateApproved})

This should give you the difference between invoice date and approval date for every record.

Then you can just hide the ones you don't want to see - right click on the formula, Format Field, Common tab. Click the X2 next to Suppress and try this:

{APUI.APRef} = previous({APUI.APRef}

That should only display the difference in days between the invoice date and the first approval date of each invoice.

 

[ginaburg]

That worked great. Thanks for all of your help.