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

    kate summer

      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
          Sherzodbek Ibragimov

          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
            Jim Dehner

            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.