Calculate Days with Minimum Date 1

[FireGeek21]

I have a couple formula fields I am trying to use so I can calculate the number of days that have lapsed. This is working to a point - if other records are edited or changed after a status of 1, it's picking up these later dates and not the status of 1 date. If after a status of 1, other records are edited or changed, I only want the date returned for a status of 1 (minimum date). If there is no status of 1, then I want the maximum date. Clear as mud? Here are my formulas...

{@REQ_DT}:
Date({ReqTable.DateField})

{@STATUS_DT}:
Date({HxTable.DateField})

{@MAX_STATUS_DT}:
IF {HxTable.Status} = 1 THEN Date({HxTable.DateField})
ELSE Maximum({@Status_DT, {ReqTable.REQ})

{@Days_Open}:
DateDiff('d', {@REQ_DT}, {@MAX_STATUS_DT})

Data sample:

Req Req_DT Status Status_DT
123 01/01/2010 4 01/03/2010
123 01/01/2010 3 01/06/2010
123 01/01/2010 7 01/10/2010

345 02/15/2010 4 02/15/2010
345 02/15/2010 3 02/20/2010
345 02/15/2010 1 02/25/2010
345 02/15/2010 7 08/01/2010

567 03/01/2010 3 03/02/2010
567 03/01/2010 1 03/26/2010

In Req 123, I would like DateDiff to calculate as:
DateDiff('d', 01/01/2010, 01/10/2010) = 10

In Req 345, I would like DateDiff to calculate as:
DateDiff('d', 02/15/2010, 02/25/2010) = 10 (don't use 08/01/2010)

In Req 567, I would like DateDiff to calculate as:
DateDiff('d', 03/01/2010, 03/26/2010) = 25

It's like I need the minimum(maximum(status_dt))???

Thanks for the help!


FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

You should use a formula like this:

//{@status1}:
if {HxTable.Status} = 1 THEN
Date({HxTable.DateField})

Then your calculation would be:

if minimum({HxTable.Status},{table.req}) = 1 then
datediff("d",{@REQ_DT},
maximum({@status1},{table.requ})) else
datediff("d",{@REQ_DT},
maximum({HxTable.DateField},{table.req}))

-LB

 

[FireGeek21]

LBass,

Thank you for the response. Looks so simple! Unfortunately, it appears the calc is occurring on the last part (on the ELSE). So, in my sample data, REQ 345 is being calculated using date 08/01/2010 when it should use date 02/25/2010.

Any other thoughts?

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

Please show all formulas exactly as you implemented them.

-LB

 

[FireGeek21]

STATUS_1:
IF {HISTORY.STATUS} = 1 THEN Date({HISTORY.LASTDATE})

REQ_DT:
Date({JOBS.POSTDATE})

FORMULA (We have a status of 0 which is why I didn't use Minimum):
IF {HISTORY.STATUS} = 1 THEN
DateDiff('d',{@REQ_DT},Maximum({@STATUS_1},{JOBS.REQ}))
ELSE
DateDiff('d',{@REQ_DT},Maximum({HISTORY.LASTDATE},{JOBS.REQ}))


This is so odd... everything looks in order but won't work. I even tried converting the History.Status to a text thinking maybe the data isn't a number. No go!


FireGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

That is the problem--the zero value--which you didn't show in your initial post. Add another formula like this:

//{@histstat}:
if {HISTORY.STATUS} = 0 THEN
9 else
{HISTORY.STATUS}

Then change the formula to:

if minimum({{@histstat},{JOBS.REQ}) = 1 then
DateDiff('d',{@REQ_DT},Maximum({@STATUS_1},{JOBS.REQ}))ELSE
DateDiff('d',{@REQ_DT},Maximum({HISTORY.LASTDATE},{JOBS.REQ}))

You HAVE to use the minimum because you are checking for the presence of a 1 anywhere in the group.

-LB

 

[FireGeek21]

Spot on LB! THANK YOU!!!

FireGeek
(currently using Crystal Reports XI with Lawson 8.03)