Subtracting X days from date

[ZoeM]

Happy Thursday Community!

I have the below syntax and I am having issues getting my desired result:

IF [VPP Dates]<DateTimeToday() and IsNull([Decl Dates]) THEN "Late" ELSE

IF [VPP Dates]<DateTimeAdd(DateTimeToday(),-14,"days") and IsNull([Decl Dates]) THEN ">14 Days Late" ELSE

"Not Late" ENDIF ENDIF

VPP date is Delivery date

Decl Date is Delivered date

The idea is that if a products delivery date is in the past and the delivered date is blank (meaning not yet delivered), then should be late. 

If the delivery date is more that 14 days in the past and the delivered date is blank (meaning not yet delivered, very bad), then should be >14 days late.

For everything else, should be not late.

I am getting the Late and Not late, but I cant seem to pull in any >14 days late.

Please Help!

Thanks in advance

[Thableaus · Accepted solution]

Hi @ZoeM 

Change this order

IF [VPP Dates]<DateTimeToday() and IsNull([Decl Dates]) THEN "Late" ELSE

IF [VPP Dates]<DateTimeAdd(DateTimeToday(),-14,"days") and IsNull([Decl Dates]) THEN ">14 Days Late" ELSE

to:

IF [VPP Dates]<DateTimeAdd(DateTimeToday(),-14,"days") and IsNull([Decl Dates]) THEN ">14 Days Late" ELSE

IF [VPP Dates]<DateTimeToday() and IsNull([Decl Dates]) THEN "Late" ELSE

It should work.

Thing is first IF is evaluated and is always going to overwrite the second IF situation, because if first IF is true, then the second one is also true (due to your logic).

Cheers,

[BenMoss]

Hi Zoe,

I think this is just an order of operations issue.

Because you are first checking if the date is less than today; which will be true for all those records less than today;

Therefore all records that flow through false are greater than today; and therefore they will not meet the 2nd criteria.

You can also use elseif rather than else if

You can simply flip it round so...

IF [VPP Dates]< DateTimeAdd(DateTimeToday(),-14,"days") and IsNull([Decl Dates]) THEN "Late" ELSEIF [VPP Dates]< datetimetoday() and IsNull([Decl Dates]) THEN ">14 Days Late" ELSE

"Not Late" ENDIF

[ZoeM]

Spectacular!

O those orders, if only hah!

Thanks for your help Ben!