alteryx Community

# Alteryx Designer Desktop Discussions

6 - Meteoroid

Hello,

I need help writing a multi nested IF statement in Alteryx based on the excel formula attached. Here is another written out version for better context:

IF(AND(OFFER DUE DATE <>1/1/1900, OFFER DUE DATE>=5/1/2023, OFFER DUE DATE<=5/31/2023),

• Value if true: IF(OR(AND(IP OFFER SUBMIT DATE<>1/1/1900, OFFER SUBMIT DATE 1 <=OFFER DUE DATE, ENTITY FEE RECEIVED 1 ="Yes"), AND(OFFER SUBMIT DATE 2 <>1/1/1900, OFFER SUBMIT DATE 2 <=OFFER DUE DATE, ENTITY FEE RECEIVED 2 ="Yes")),
• Value if true: “TRUE”,
• Value if false: IF(AND(OFFER SUBMIT DATE 1 <>1/1/1900, OFFER SUBMIT DATE 1 <=OFFER DUE DATE, ENTITY FEE RECEIVED 1 ="Yes", OFFER SUBMIT DATE 2 <>1/1/1900, OFFER SUBMIT DATE 2 <=OFFER DUE DATE, ENTITY FEE RECEIVED 2 ="Yes"),
• Value if true: IF(AND(OFFER SUBMIT DATE 1 >= OFFER SUBMIT DATE 2, OFFER SUBMIT DATE1 >=5/1/2023, OFFER SUBMIT DATE 1 <=5/31/2023),
• Value if true: “TRUE”,
• Value if false: IF(AND(OFFER SUBMIT DATE 1 < OFFER SUBMIT DATE 2 , OFFER SUBMIT DATE 2 >=5/1/2023, OFFER SUBMIT DATE 2 <=5/31/2023),
• Value if true: “TRUE”,
• Value if false: “FALSE”)))))

I have tried many times but can't seem to get the syntax right. Do I need to break it out into multiple different formulas? If so, how would I write the nested true and false statements?

Thank you!!!

2 REPLIES 2
8 - Asteroid

I think this is the formula you're looking for based on the what I can tell from above, at least if anything it should get you going in the right direction for formatting in the formula tool.

``````IF( [Offer Due Date] != '1/1/1900' && [Offer Due Date] >= '5/1/2023' && [Offer Due Date] <= '5/31/2023')
THEN "TRUE"
ELSE IF(([Offer Submit Date 1] != '1/1/1900' && [Offer Submit Date 1] <= [Offer Due Date] && [Entity Fee Received 1] ="Yes") || ([Offer Submit Date 2] != '1/1/1900' && [Offer Submit Date 2] <= [Offer Due Date] && [Entity Fee Received 2] ="Yes"))
THEN "TRUE"
ELSE IF([Offer Submit Date 1] != '1/1/1900' && [Offer Submit Date 1] <= [Offer Due Date] && [Entity Fee Received 1] ="Yes" && [Offer Submit Date 2] != '1/1/1900' && [Offer Submit Date 2] <= [Offer Due Date] && [Entity Fee Received 2] ="Yes")
THEN "TRUE"
ELSE IF( [Offer Submit Date 1] >= [Offer Submit Date 2] && [Offer Submit Date 1] >= '5/1/2023' && [Offer Submit Date 1] <= '5/31/2023')
THEN "TRUE"
ELSE IF( [Offer Submit Date 1] >= [Offer Submit Date 2] && [Offer Submit Date 1] >= '5/1/2023' && [Offer Submit Date 1] <= '5/31/2023')
THEN "TRUE"
ELSE IF([Offer Submit Date 1] < [Offer Submit Date 2] && [Offer Submit Date 2] >= '5/1/2023' && [Offer Submit Date 2] <= '5/31/2023')
THEN 'TRUE'
ELSE 'FALSE'
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF``````

12 - Quasar

Hello, @emartinez.

First, I'd write out the logic in natural language in order to clarify the request.

Second, if you take the route of a nested statement, I believe it may be easiest to first approach it by creating many fields, one logical step per field. Once you get your desired result, you can always go back and condense the formulas if you want.

For example, I made a Boolean field to determine if the Offer Due Date is between two dates.

Also, you can use User Constants instead of hardcoding within the Formula tool. May not make much of a difference now, but it is something I use on occasion.

Please let me know if this helps.

Labels