5 Replies Latest reply on Aug 5, 2018 9:50 AM by Ankit Bansal

# Calculated Field / Multiple ID's / Different Categories

I have a question. I would typically use some COUNIF's in excel, but I'm not sure how to work this in Tableau.

There are three columns of data: ID, Category, and Status. The ID may appear multiple times (rows), each with a different category, but same status. The are three categories... A,B,C and two status "Yes" and null.

Sample Data:

131A, B, Null

2A13, C, Yes

2A13, A, Yes

2A13, B, Yes

12B4, A, Yes

I need to create a calculated field that will display a "1" if the ID has a status of null, a "2" if the ID has a category of "B" on any row, and a "3"  otherwise.

Sample Data with calculated field:

131A, B, Null, 1

2A13, C, Yes, 2

2A13, A, Yes, 2

2A13, B, Yes, 2

12B4, A, Yes, 3

Any suggestions would be great. Displaying the "1" is easy. I'm not sure how to check all the rows of data to see if any row has the same ID and then check the category.

• ###### 1. Re: Calculated Field / Multiple ID's / Different Categories

Hi Drew,

1. Check for status B = {FIXED [ID]:max(if [Category]="B" then 1 ELSE 0 END)}

2. Result :

if [Status]=NULL then 1

ELSEIF [Check for status B]=1 then 2

else 3

END

• ###### 2. Re: Calculated Field / Multiple ID's / Different Categories

When I try to created a calculated filed using this.... "{FIXED [ID]:max(if [Category]="B" then 1 ELSE 0 END)}" I get back "undefined" for all the rows. Any idea what would cause this?

Thank you so much for the help!

• ###### 3. Re: Calculated Field / Multiple ID's / Different Categories

Hi Drew,

You can check the attached file.

• ###### 4. Re: Calculated Field / Multiple ID's / Different Categories

Thank you for sending

On reviewing your data it appears that it was done at the aggregate level and comes back as undefined in your data as well. Is there any way around this?

• ###### 5. Re: Calculated Field / Multiple ID's / Different Categories

Drew,

This should work for you:

{fixed [ID] : min(if isnull([Status]) then 1 elseif  [Category]='B' then 2 else 3 end) }

Thanks,

Ankit Bansal