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

Developer

Expression

Data Investigation

Accepted answers

Thableaus

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,

All comments

Thableaus

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!

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335