4 Replies Latest reply on Dec 4, 2017 2:00 PM by Elsa van Latum

# Creating new variable with IF formula?

Hi Community,

I am trying something out with Tableau and I would love to get someone's advice on this. I have a data set containing Human Services in New York City. The geographical level on which this data is available doesn't allow me to work with Census data, so I am trying to combine them into larger units. The geographical level available in the data set is NTA (Neighborhood Tabulation Areas). These need to be combined into PUMA districts. PUMA districts can contain anything between 2-5 NTAs.

In the spreadsheet added to this post, the first sheet contains the raw data. One of the columns contains the NTA code. The second sheet contains the data I have used in my Tableau analysis. The third sheet contains the crosswalk between NTA and PUMA districts.

The goal of my analysis is to create a table similar to the one you can access here: Tableau Public. See also screenshot below. Instead of using 'Council'  as my Column variable, however, I am interested in using PUMA here. This means I have to combine multiple NTAs to create a new PUMA variable. I started computing a new dimension with the following code:

IF

[Nta] = 'BX22, BX28, BX29' THEN '3701'

ELSEIF

[Nta] = 'BX03, BX44, BX62, BX99' THEN '3702'

END

I would have to write this formula out for all PUMA codes to create the new variable. So far, the problem is that when I use the new variable 'PUMA' as my Column variable, it doesn't split up into the 2 categories I defined.

Perhaps I am going about this in the wrong way so I would love to get someone's thoughts on how to perform this task the correct way.

I am very grateful for any help!

Elsa

• ###### 1. Re: Creating new variable with IF formula?

You could write it like this:

IF

[Nta] = 'BX22'  or [Nta] = 'BX28'  or [Nta] = 'BX29' THEN '3701'

ELSEIF

[Nta] = 'BX03'  or [Nta] = 'BX44'  or [Nta] = 'BX62'  or [Nta] = 'BX99' THEN '3702'

END

But I would prefer to write it like this:

Case [Nta]

when 'BX22' then '3701'

when 'BX28' then '3701'

when 'BX29' then '3701'

when 'BX03' then '3702'

when 'BX44' then '3702'

when 'BX62' then '3702'

when 'BX99' then '3702'

end

I think the code is easier to read that way, certainly easier to maintain

1 of 1 people found this helpful
• ###### 2. Re: Creating new variable with IF formula?

try this

IF

[Nta] = 'BX22'

OR

[Nta] = 'BX28'

OR

[Nta] = 'BX29'

THEN '3701'

ELSEIF

[Nta] = 'BX03'

OR

[Nta] = 'BX44'

OR

[Nta] = 'BX62'

OR

[Nta] = 'BX99'

THEN '3702'

END

1 of 1 people found this helpful
• ###### 3. Re: Creating new variable with IF formula?

Thank you! This helped. I got exactly the result I wanted.

• ###### 4. Re: Creating new variable with IF formula?

Excellent, thank you so much!