1 2 Previous Next 18 Replies Latest reply on Jul 5, 2019 1:47 AM by Dhilip Subramanian

# How to create nested IF statements in Calculation field?

Hi

I have a dataset and it has two columns.

1) Year Column from 2008 to 2018

2) Team 1 score column for all the years.

I need to make a graph for minimum scores for each column. I have zero in 2008, 2009, 2012 columns. I would like to remove this zero and assign 56 for 2008, 67 for 2009, 82 for 2012.

I used the IF condition

if [Year] = '2008' and [Team1 score] = 0 then 56 else [Team1 score] END

if [Year] = '2009' and [Team1 score] = 0 then 67 else [Team1 score] END

if [Year] = '2012' and [Team1 score] = 0 then 82 else [Team1 score] END

The above code is not working and I cannot make the graph.

Could you please advise how to do this? Also, please guide me on how to delete or filter out zero values in the row?

Thanks

Regards,

Dhilip

• ###### 1. Re: How to create nested IF statements in Calculation field?

Hi Dhilip,

if [Year] = '2008' and [Team1 score] = 0 then 56 else [Team1 score]

ELSEIF [Year] = '2009' and [Team1 score] = 0 then 67 else [Team1 score]

ELSEIF [Year] = '2012' and [Team1 score] = 0 then 82 else [Team1 score]

END

This will do the trick hope this helps

BR,

NB

• ###### 2. Re: How to create nested IF statements in Calculation field?

Hi Naveen

I am getting below error for the above code

EXPECTED 'END' TO MATCH 'if' at character 0.

• ###### 3. Re: How to create nested IF statements in Calculation field?

Hi Dhilip,

If we have only one condition to met , then only we can use If and else.

But in you case You have multiple condition to met then you should go if - else if  statement which is totally different from nested if, As per your terminology nested if means : if inside another if .

IF [Year] = '2008' and [Team1 score] = 0 then 56

ELSEIF [Year] = '2009' and [Team1 score] = 0 then 67

ELSEIF [Year] = '2012' and [Team1 score] = 0 then 82

Else [Team1 score]

END

and this for your second question regarding null , find the below link and it will give you best thinking approach

Nulls Handling - String, Date, Numerical Data types

Hope it Helps !!

Thanks,
Prashant

• ###### 4. Re: How to create nested IF statements in Calculation field?

Hi Prashant

Getting error  EXPECTED 'END' TO MATCH 'if' at character 0.

• ###### 5. Re: How to create nested IF statements in Calculation field?

i think you are comparing with string and in else part its taking as integer , thats why this one is occuring.

Share your calculation screenshot with data type , which you are using in tableau

• ###### 6. Re: How to create nested IF statements in Calculation field?

Hi

• ###### 7. Re: How to create nested IF statements in Calculation field?

IF [Year] = '2008' and [Team1 score] = 0 then 56 else [Team1 score] (existed line)

try to remove else part : like this

IF [Year] = '2008' and [Team1 score] = 0 then 56

check and let me know.

Thanks

• ###### 8. Re: How to create nested IF statements in Calculation field?

don't give space after else and if , elseif

BR,

NB

• ###### 9. Re: How to create nested IF statements in Calculation field?

Getting error

• ###### 10. Re: How to create nested IF statements in Calculation field?

I tried still gives the same error

• ###### 11. Re: How to create nested IF statements in Calculation field?

Copy and paste this one :

IF [Year] = '2008' and [Team1 score] = 0 then 56

ELSEIF [Year] = '2009' and [Team1 score] = 0 then 67

ELSEIF [Year] = '2012' and [Team1 score] = 0 then 82

Else [Team1 score]

END

Thanks

• ###### 12. Re: How to create nested IF statements in Calculation field?

Now getting error can't compare date and string.. I converted Year column into date

• ###### 13. Re: How to create nested IF statements in Calculation field?

Which version are you using, i am unable to open this.

IF STR([Year]) = '2008' and [Team1 score] = 0 then 56

ELSEIF STR([Year]) = '2009' and [Team1 score] = 0 then 67

ELSEIF STR([Year]) = '2012' and [Team1 score] = 0 then 82

Else [Team1 score]

END

1 of 1 people found this helpful
• ###### 14. Re: How to create nested IF statements in Calculation field?

Hi Code is working.

But I am unable to get the minimum value in the graph when I selected the option of minimum in the measure. Hence, I cannot see the minimum value for the year 2008, 2009.  I am using Tableau 2019 public version.

any suggestions?

1 2 Previous Next