community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Subtracting X days from date

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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,

 

 

Alteryx Certified Partner
Alteryx Certified Partner
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
Asteroid

Spectacular!

O those orders, if only hah!

Thanks for your help Ben!

Labels