If and Or formula help

[wonka1234]

hi,

can anyone help me with this formula?

test field = IF [c_Action_Date] <= "2021-11-13" then [202110] Or [202111] or [202201] or [202202]

I am trying to do the above logic.

But Or doesnt really work here.

this is a record with action ddate < 2021-11-13.

so my test field is showing "0" for this formula I  have. But I want it to show "Y" if one of the 4 fields have  a "Y"

How can I do this?

[Qiu]

@wonka1234 
Can you give a bigger dataset for the input and output?

[wonka1234]

@Qiu  see attached.

max diamond date = 

IF [c_Action_Date] <= "2021-11-13" then [202110] Or [202111] or [202201] or [202202]
elseif [c_Action_Date] <= "2021-12-13" then [202203]
elseif [c_Action_Date] <= "2022-01-13" then [202204]
elseif [c_Action_Date] <= "2022-02-13" then [202205]
elseif [c_Action_Date] <= "2022-03-13" then [202206]
else ""
ENDIF

min diamond date = 

IF [c_Action_Date] <= "2021-11-12" then [202110]
elseif [c_Action_Date] <= "2021-12-12" then [202111]
elseif [c_Action_Date] <= "2022-01-12" then [202112]
elseif [c_Action_Date] <= "2022-02-12" then [202201]
elseif [c_Action_Date] <= "2022-03-12" then [202202]
elseif [c_Action_Date] <= "2022-04-12" then [202203]
elseif [c_Action_Date] <= "2022-05-12" then [202204]
elseif [c_Action_Date] <= "2022-06-12" then [202205]
elseif [c_Action_Date] <= "2022-07-12" then [202206]
else ""
ENDIF

all attached records have action date less then 2022-11-13

[binuacs]

@wonka1234 I don't see the field  [c_Action_Date]  in the given dataset. 

[FilipR]

IF [c_Action_Date] <= "2021-11-13" AND

([202110] = 'Y' OR [202111] = 'Y' OR [202201] = 'Y' OR [202202] = 'Y')

THEN 'Y' ELSE '' ENDIF

[wonka1234]

@FilipR  would you be able to include my full formula with yours?

[wonka1234]

@binuacs 

please see attached.

[FilipR]

@wonka1234 Your formula doesn't make sense here:

You cannot have an OR statement in the result part (after THEN). 

[FilipR]

@wonka1234 To get where you need you just add ELSEIFs, like this:

IF [c_Action_Date] <= "2021-11-13" AND ([202110] = 'Y' OR [202111] = 'Y' OR [202201] = 'Y' OR [202202] = 'Y')

ELSEIF [c_Action_Date] <= "2021-12-13" AND [202203] = 'Y'

THEN 'Y' ELSE '' ENDIF

[Emmanuel_G]

@wonka1234 

I don't quite understand the purpose of saying you want 202110 or 202111 or 202201 or 202202.

For your logic to work, between each OR, you would have to do Boolean tests so [202110] = value OR [202111] = value OR [202201] = value and so on.

Do you want to have a result of all these concatenated columns? Because if you do so many "OR" it's because taking the result of one of the columns or another doesn't matter so it's better to choose only one at random among all these 4, that's it more sense no?