Nested IFs and logic...

[pglover]

Another beginner question no doubt - so apologies to start with.

Report requires comparing date fields (SLA success rate)-
RequiredDate
TargetDate
CloseDate

To calculate the SLA success, I have used a formula field;
If CloseDate <= RequiredDate then 1 else
if CloseDate <= TargetDate then 1 else 0

This is successful where there is a CloseDate. But what if the CloseDate is still empty (ie call open) and the TargetDate has been and gone? I have tried v unsuccessfully with some Date functions and would appreciate any help or guidance.

Many thanks

Penny

 

[lbass]

I would replace {table.closedate} with {@closedate} and then substitute it in your formula:

if isnull({table.closedate}) or
{table.closedate} = Date(0,0,0) then CurrentDate
else {table.closedate}

Do you care whether the close date meets one of the criteria and not the other? The close date will not be evaluated for the second criterion (close date < target date) if it meets the first criterion.

-LB

 

[Ngolem]

no apologies needed...we all started somewhere

*****************************

To calculate the SLA success, I have used a formula field;
If CloseDate <= RequiredDate then 1 else
if CloseDate <= TargetDate then 1 else 0

This is successful where there is a CloseDate. But what if the CloseDate is still empty (ie call open) and the TargetDate has been and gone? I have tried v unsuccessfully with some Date functions and would appreciate any help or guidance.

*****************************

Well,let us start with the beginning

What value do you want to assign to your &quot;SLA Success rate&quot; under these conditions?? Is 0 a valid result for both conditions or do you want to distiguish between an &quot;Call Open&quot; and &quot;TargetDate that has expired&quot;??

My thoughts are that you would want to know the difference.

Also you are showing the same result for the comparison with RequiredDate and TargetDate....don't you want to know which comparison generated the positive result? or maybe it isn't important...

Is &quot;CloseDate&quot; left empty until a transaction is completed??
If so then I suppose this is handled by a comparison to the current date...if it is less than the Required or Target dates then the transaction is still &quot;LIVE&quot; otherwise it is a missed opportunity.

Let us construct the complete formula

//@SLA_Success_Rate

WhilePrintingRecords;
NumberVar SLA; //this will contain our result

//first test is on a non-null closedate
if not isnull{{table.Closedate}} or
length(Trim({table.Closedate}) <> 0 then
(
if {table.Closedate} <= {table.RequiredDate} or
{table.Closedate} <= {table.TargetDate} then
SLA := 1 //Positive 1 identifies this condition
else
SLA := -1; //Negative 1 identifies a failure
)
// this handles the case where Closedate is Blank or Null
else if currentDate <= {table.TargetDate} then
SLA := 0 //identifies &quot;open Call&quot;
else
SLA := -9999; //unreasonable neg number shows
//missed opportunity

SLA;

hope this helps you

*****************



Jim Broadbent

 

[pglover]

Thanks LB -
What I'm after is the percentage of calls that were closed within the SLA period; so if the call is closed before the target date (the SLA date) or before the required date then it has met the SLA and is successful. If it fails to meet the required date AND the target date (if different) then it fails. By assigning a 1 to successful and 0 to failures, I had assumed that a simple SUM would give me the basis of the %age SLA success.

I think I understand what you're suggesting; use the current date if closedate is blank or null, thus checking if still within time frame or not? Like it.

Thank you.

 

[pglover]

Wow Jim

That's blown my mind away - but to try and answer the questions...
The aim is to get a %age for the number of calls that have met the SLA and (obviously) the number that failed. So if the TargetDate has expired then it has failed. At present there is no need to differentaite between target and required date. CloseDate is only completed at the end of the transaction (as you surmised).

Ermm , sorry to admit it but I don't really understand the rest of your post.. I think I get the bit in the middle, but I'm not sure of how to display the result (I really did mean a beginner, sorry Jim.)

Would be quite happy to take off-line if necessary....

 

[pglover]

LB

When I tried your answer

if isnull({table.closedate}) or
{table.closedate} = Date(0,0,0) then CurrentDate
else {table.closedate}

I get a message saying a string is required here (cursor at beginning of Date). I've tried the Help section of Crystal but it doesn't seem to suggest much.

TIA

 

[Ngolem]

ok....the only thing I want to know is how you want to handle an &quot;Open Call&quot;...

I will assume an &quot;Open Call&quot; is just an &quot;undetermined success&quot; ... ie for this report it is counted in the total but not as a success

Since you are looking for the % success rate we can do all of that in the formula.


//@SLA_Success_Rate (placed Suppressed in detail section)

WhilePrintingRecords;
NumberVar SLA := 0; //this will contain success count
NumberVar Total := 0;

Total := Total + 1; //always one to the Total
if not isnull{{table.Closedate}} or
length(Trim({table.Closedate}) <> 0 then
(
if {table.Closedate} <= {table.RequiredDate} or
{table.Closedate} <= {table.TargetDate} then
SLA := SLA + 1; //this is a success condition
);

this simplifies the formula quite a bit since you are only interested in adding successful occurances

Now to display your % successrate place this formula in a suitable footer section

//@Display_%SLA_Success

WhilePrintingRecords;
NumberVar SLA ;
NumberVar Total ;

if Total = 0 then
&quot;0.00%&quot;
else
totext(SLA/Total*100,2) + &quot;%&quot;;

that should do it for you

BTW... &quot;//&quot; in a Crystal formula is a comment marker





Jim Broadbent

 

[IanWaterman]

Penny

You could try adding the condition
if isnull(closedate0 then 1 else 0

Ian Waterman
UK Crystal Consultant

 

[IanWaterman]

Penny

You could try adding the condition
if isnull(closedate) then 1 else 0

Ian Waterman
UK Crystal Consultant

 

[lbass]

It looks like your date is a string, not a true date, and so change my earlier formula {@closedate} to:

if isnull({table.closedate}) or
{table.closedate} = &quot;&quot; then totext(CurrentDate) else
{table.closedate}

Then use {@closedate} instead of {table.closedate} in {@SLASuccess}:

if {@closedate} <= {table.RequiredDate} or
{@closedate} <= {table.TargetDate} then 1 else 0

You can then right click on {@SLASuccess} and insert a sum and/or a percentage of the total.

-LB

 

[pglover]

Thanks very much to all who answered;

Problem resolved using LBs suggestion, but Jim has given me the idea for the next report (dangerous that, Jim)

Hope we aren't limited to the number of questions we can ask


Regards

Penny