3 Replies Latest reply on Oct 10, 2017 8:00 AM by Jim Dehner

# How to create a categorical variable based on two numeric calculations

Hi,

I am trying to create a stacked bar of enrollment showing how many students are in major vs. out of the major. I created two calculations InMajor and NotInMajor. In order to build the stacked bar I think I need to create a categorical variable based on the InMajor and NotInMajor calculations. How to do that? It is like I have the count of Male and Female, how to create a gender dimension variable. A sample workbook is attached. Thank you for any suggestions!

• ###### 1. Re: How to create a categorical variable based on two numeric calculations

Kate,

I hope I understand your requirements correctly. You can achieve that by pivoting InMjaor and OutMajor and you will get 2 new fields> Pivot Name and Pivot Value. Then you can use pivot value as bar chart and pivot name to color it to get stacked bar. Here is some instruction to remind you how to do pivot in Tableau: Pivot Data from Columns to Rows

Hope it helps.

• ###### 2. Re: How to create a categorical variable based on two numeric calculations

Hi Kate

is this what you wanted

If it is you were almost there

I took you in and out field and combined them into a single field and then used an LOD expression to count the  members in each group

the first calc is messy but it is just yours and I assigned an string "in major' or 'not in major' to each field

(  if

(STARTSWITH([Prog], 'CHEM') AND (CONTAINS([Subject],'CHEM') OR CONTAINS([Subject],'BIOCH')))

or (STARTSWITH([Prog],'LAAST')AND (CONTAINS ([Subject],'LALS') OR CONTAINS ([Subject],'SPAN')))

THEN 'In Major'

elseif  not

((STARTSWITH([Prog], 'CHEM') AND (CONTAINS([Subject],'CHEM') OR CONTAINS([Subject],'BIOCH')))

or (STARTSWITH([Prog],'LAAST')AND (CONTAINS ([Subject],'LALS') OR CONTAINS ([Subject],'SPAN')))

THEN 'Not in Major' ELSE NULL END )

The LOD is           { INCLUDE [InMajor indicator]:countd([ID])}

Then I just created the Viz by counting the LOD and putting the messy sorting calc on the color tile - you can break it out a lot of ways I just did it by Subject

and you were right you needed to create a single field similar to the male/female field you referenced

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: How to create a categorical variable based on two numeric calculations

Thanks Kate