ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

If Statement with multiple AND Statement

ZoeM
8 - Asteroid

Good morning Community.

I am fighting with establishing an If Statement combined with 'AND' or 'OR' statements to get desired results.

In the sample data attached, the key metric is Item Type (Shirt, Pants). 

The column 'Item Type Status to Date Required By' (column F) should be populated based on whether the associated Item type was able to be manufactured by the 'Date Required By' or sooner.

Key scenarios:

If the Planned Manufactured Date falls after the Required By Date, then it is late. 

If the Actual Manufactured Date falls after the Required By Date, then it is late. 

If the Actual Manufactured Date is Blank and the Date Required By is past then it is late. 

 

I Hope this is sufficient explanation?

 

Thanks in advance. 

 

 

ChrisTX
13 - Pulsar

Use a Formula tool with logic like this:

 

If [Planned Manufactured Date] > [Required By Date] THEN "Late". 

ELSEIF [Actual Manufactured Date] > [Required By Date\ THEN "Late". 

ELSEIF IsNull([Actual Manufactured Date]) and [Date Required By] < DateTimeNow() THEN "Late"

ELSE "Not Late"

ENDIF 

 

Chris

afv2688
16 - Nebula
16 - Nebula

hello @ZoeM ,

 

Does this work for you?:

 

Untitled.png

 

Regards

MarqueeCrew
19 - Altair
19 - Altair

@ZoeM ,

 

Sorry to hear that you're fighting.  Be Happy!

 

Here is an alternative expression to what @ChrisTX solved your challenge with:

 

IF 
   (
   [Planned Manufactured Date] > [Required By Date]
   OR
   [Actual Manufactured Date]  > [Required By Date]
   )
   OR 
   (
   IsNull([Actual Manufactured Date]) 
   and 
   [Date Required By] < DateTimeNow()
   ) THEN "Late"

ELSE "Not Late"

ENDIF 

 

Cheers,

 

Mark 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Please Subscribe to my youTube channel.
ZoeM
8 - Asteroid

As they say there is more than one way to slice a pie...

I used a variation of your formula, had to duplicate it because there was criteria for the Shirts and criteria for the Pants. But worked like a charm.

 

Thanks for your time and consideration. 

 

IF
(
[<AR> Approval Date] > [PSC]
OR
[AR Completed Date] > [PSC]
)
OR
(
IsNull([AR Completed Date])
and
[PSC] < DateTimeNow()
) THEN "Late"

ELSE

IF
(
[<AR> Approval Date] > [TSC]
OR
[AR Completed Date] > [TSC]
)
OR
(
IsNull([AR Completed Date])
and
[TSC] < DateTimeNow()
) THEN "Late"

ELSE

"Not Late"

ENDIF ENDIF

Labels