Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Subtracting X days from date

ZoeM
8 - Asteroid

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

3 REPLIES 3
Thableaus
17 - Castor
17 - Castor

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
ACE Emeritus
ACE Emeritus
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
8 - Asteroid

Spectacular!

O those orders, if only hah!

Thanks for your help Ben!

Labels