Required help in IF Statement

Hi,

I am trying to create new column based on my below condition. however, this is not working in formula tool. Can someone please help me correcting the IF statement.

IF Contains([Period], "One YearEnded") then "Cumulative" ELSEIF Contains([Period], "_2" and "One YearEnded") then "Annulized"
ELSE "Annulized" ENDIF

(Screenshot attached)

Screenshot 2024-01-29 184223.png

Help

Accepted answers

HomesickSurfer

Hi @anonymous008_G

My approach:

This:

IF Contains([Period], "One YearEnded") AND !Contains([Period], "_") THEN "Cumulative" ELSE "Annualized" ENDIF

or this:

IF Contains([Period], "Years") OR Contains([Period], "_")  THEN "Annualized" ELSE "Cumulative" ENDIF

alexnajm

@anonymous008_G my most recent answer meets these requirements!

Required help in IF Statement.png

Required help in IF Statement.yxmd

All comments

alexnajm

Your screenshot doesn’t end with ENDIF!

alexnajm

But also you need to separate your contains statement - Contains([Period], “_2”) OR Contains([Period], “One YearEnded”)

IF Contains([Period], "One YearEnded") then "Cumulative" ELSEIF Contains([Period], “_2”) OR Contains([Period], “One YearEnded”) then "Annulized"
ELSE "Annulized" ENDIF

aatalai

been there done that

aatalai

@alexnajm couldn't the if statement be IF Contains([Period], "One YearEnded") then "Cumulative"
ELSE "Annulized" ENDIF

as at the orginal esle if either way it would be "Annulized" - your way would work, but this would just be a bit more efficent?

anonymous008_G

Hi Alex --- thanks for this. Now the conditional formula working fine but end result is not as expected. In my column there are values One YearEnded12/31/23 and One YearEnded12/31/23_2. I want to differentiate this as Cumulative and Annualized respectively. However, using your corrected formula I get both as Cumulative. Could you please help further. I even try replacing OR with AND but this doesn't worked.

anonymous008_G

Thanks for your input but this way also not fulfilling my end goal as explained to Alex in my recent comment.

alexnajm

@aatalai yes I agree that would work and be simpler - I just worked on correcting what was in front of me 😊

@anonymous008_G thats correct - that's because your original IF statement doesn't differentiate between the One YearEnded values. Why can't you just do IF Contains([Period],"_2") THEN "Annualized" ELSE "Cumulative" ENDIF ?

aatalai

@alexnajm team work makes the dream work

anonymous008_G

Apologies for providing limited information earlier, but your recent solution also wouldn't work since column contains few other values as well as per below table. My expected output to be all the values which contain "One Year Ended" in period column should be populated as cumulative in the new column and rest other should populate as Annualized including the one "One "YearEndedMM/DD/YY_2".

Period	Return Type
One YearEnded12/31/23	Cumulative
One YearEnded12/31/22	Cumulative
One YearEnded12/31/21	Cumulative
One YearEnded12/31/20	Cumulative
One YearEnded12/31/19	Cumulative
One YearEnded12/31/18	Cumulative
One YearEnded12/31/17	Cumulative
One YearEnded12/31/16	Cumulative
One YearEnded12/31/15	Cumulative
One YearEnded12/31/14	Cumulative
One YearEnded12/31/23_2	Cumulative
Two YearsEnded12/31/23	Annulized
Three YearsEnded12/31/23	Annulized
Four YearsEnded12/31/23	Annulized
Five YearsEnded12/31/23	Annulized
Ten YearsEnded12/31/23	Annulized

alexnajm

Very helpful and needed context - based on your table's values, try this: IF Contains([Period],"One YearEnded") THEN "Cumulative" ELSE "Annulized" ENDIF

HOWEVER your description of "One "YearEndedMM/DD/YY_2" doesn't match the value in the table - if this is the case, it needs to be adapted: IF Contains([Period],"One YearEnded") AND !Contains([Period],"_2") THEN "Cumulative" ELSE "Annulized" ENDIF

aatalai

@anonymous008_G can you please help clarify what your desired outcome is for Two YearsEnded12/31/23?

Is it Annualized YearEnded12/31/23_2

and for Four YearsEnded12/31/23 would it be Annualized YearEnded12/31/23_2

HomesickSurfer

Hi @anonymous008_G

My approach:

This:

IF Contains([Period], "One YearEnded") AND !Contains([Period], "_") THEN "Cumulative" ELSE "Annualized" ENDIF

or this:

IF Contains([Period], "Years") OR Contains([Period], "_")  THEN "Annualized" ELSE "Cumulative" ENDIF

anonymous008_G

I want end result as if column contains One YearEnded then Cumulative , if column contain One YearEnded and _2 then Annualized else everything should be annualized. In the raw data One YearEnded12/31/23 this value is double differentiating one value for cumulative and other value which converted as _2 by alteryx is annualized. I don't want any dependencies on date since this is going to change every year.

alexnajm

@anonymous008_G my most recent answer meets these requirements!

Required help in IF Statement.png

Required help in IF Statement.yxmd

alexnajm

@anonymous008_G I would appreciate one of my posts also being marked as a solution since the same function (bar "_2" instead of "_") was mentioned prior (and you can accept multiple posts as solutions). Thank you in advance!

anonymous008_G

thank you all. this is works now

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703