Looking for late approved cases through IF THEN ELSE

[Anonymous]

Hi all,

I have cmade this script so it can look if the case is late approved and if a revised date have been agreed then this is used as "new" due date" 

if([Revised due date]='', if(date#([Due date], 'MMM DD, YYYY') < date#([Approval date], 'MMM DD, YYYY'), 1, 0), if(date#([Revised due date], 'MMM DD, YYYY') < date#([Approval date], 'MMM DD, YYYY'), 1, 0)) as LateApproved,

Below is an example how it should count

Due date            Revised due date      Approved date     Outcome

Jan 1, 2015        Jan 30, 2015             Jan 25, 2015        No not count

Jan 1, 2015                                          Jan 25, 2015       Do count

Jan 1, 2015        Jan 30, 2015              Feb 2, 2015        Do count

But it seems not to work..... can you see a clear error in the script?

Thanks

Peter

[sunny_talwar]

Try this may be:

If(Len(Trim([Revised due date])) = 0,

If(Date#([Due date], 'MMM DD, YYYY') < Date#([Approval date], 'MMM DD, YYYY'), 1, 0),

If(Date#([Revised due date], 'MMM DD, YYYY') < Date#([Approval date], 'MMM DD, YYYY'), 1, 0)) as LateApproved,

[Kushal_Chawda]

try this

=if(date#([Revised due date], 'MMM DD, YYYY') < date#([Approval date], 'MMM DD, YYYY'), 1,

if(len(trim([Revised due date]))<=0,

if(date#([Due date], 'MMM DD, YYYY') < date#([Approval date], 'MMM DD, YYYY'), 1,0)))  as LateApproved,

[sunny_talwar]

This script seems to be working:

Table:

LOAD *,

  If(Len(Trim([Revised due date])) = 0,

  If(Date#([Due date], 'MMM DD, YYYY') < Date#([Approval date], 'MMM DD, YYYY'), 1, 0),

  If(Date#([Revised due date], 'MMM DD, YYYY') < Date#([Approval date], 'MMM DD, YYYY'), 1, 0)) as LateApproved;

LOAD * Inline [

Due date|            Revised due date|      Approval date|     Outcome

Jan 1, 2015|        Jan 30, 2015|             Jan 25, 2015|        No not count

Jan 1, 2015|                    |             Jan 25, 2015|       Do count

Jan 1, 2015|        Jan 30, 2015|              Feb 2, 2015|        Do count

] (delimiter is |);

[sasiparupudi1]

Approval date should be Approved date

load *,

  if([Revised due date]='', if(date#([Due date], 'MMM DD, YYYY') < date#([Approved date], 'MMM DD, YYYY'), 1, 0), if(date#([Revised due date], 'MMM DD, YYYY') < date#([Approved date], 'MMM DD, YYYY'), 1, 0)) as LateApproved

Inline

[

Due date|Revised due date |Approved date | Outcome

Jan 1, 2015|Jan 30, 2015|Jan 25, 2015|No not count

Jan 1, 2015|Jan 25, 2015||Do count

Jan 1, 2015|Jan 30, 2015|Feb 2, 2015|Do count

](delimiter is |);

[sasiparupudi1]

if(len(trim([Revised due date]))>0 and (date#([Revised due date], 'MMM DD, YYYY')<date#([Approval date], 'MMM DD, YYYY')),1,

   if(len(trim([Revised due date]))=0 and (date#([Due date], 'MMM DD, YYYY')<date#([Approval date], 'MMM DD, YYYY')),1,0)) as LateApproved;