9 Replies Latest reply on Jan 19, 2017 7:13 AM by Jamieson Christian

    Multiple Else if in a calculated field

    Sameer Mehrotra

      Hi,

       

      I am a beginner to Tableau and looking for some help with IF/ELSEIF function for calculated fields. I am currently using 10.0.1 version of Tableau Desktop.

       

      I want to create a Calculated field 'Workforce Category' based on different combination of values in 3 existing fields:

       

      1. Emp Flag
      2. Category1
      3. JFF Type

       

      All together their are 8 possible Categories. Below is the formula that I have tried. I have only gone up to 4 categories in the below formula as I noticed I am not getting anything after the first 2 categories (A & B) and then everything else is getting bucketed into the last category.

       

      Can I please get some help with it? Can I only use one ELSEIF instance or have I written the formula wrong? I have done lot of google search and searched this community as well but couldn't find the answer to my question.

       

      I can confirm there is definitely data for all these categories as I have checked the raw data manually.


      Here is the formula I have written:

       

       

      Thank you for the help in advance.

       

      Regards

       

      Sameer

        • 1. Re: Multiple Else if in a calculated field
          Chris McClellan

          Personally, I would write the CONTAINS function the other way, as in CONTAINS([JFF Type], "search string"), but that's not going to fix your problem.

           

          To help debug, I would create a new sheet that shows [Emp Flag], [Category1], [JFF Type] and [Workforce Category] - is there a trailing space ? are all the upper/lower case correct ?, etc. ...

           

          But to answer your other question, there is no limit to the number of ELSEIFs you can have in your formula.

          • 2. Re: Multiple Else if in a calculated field
            Sameer Mehrotra

            Hi Chris thank you so much for taking your time to look at my question. I will double check my formulas to make sure there are no issues with spacing or case etc.

             

            However, I am not sure what do you mean by creating a new sheet? I do have a dashboard where I have used these fields as filters to get to the desired category. But I wanted to create this new field so that I can display all categories in one go.

            • 3. Re: Multiple Else if in a calculated field
              Chris McClellan

              Ah sorry, I didn't explain myself well .... when I'm debugging calculated fields that aren't working correctly I create another sheet to show all the steps.

               

              A simpler example is X = A+B/C

               

              I've done it, and I think everyone has done it, where you "know" it's going to work properly, but it doesn't .. maybe in just a handful of cases.

               

              I create a new sheet, with A, B, C and X and then you can walk through all the scenarios that Tableau sees to try to understand where the answer is wrong.

               

              In your case you can filter the new sheet down to Category Other because that's where it's not working properly.  Hopefully this helps you to find the error quicker, and then you just delete the sheet because it's only there as a debugging tool

              • 4. Re: Multiple Else if in a calculated field
                Jamieson Christian

                Actually Chris, rearranging those parameters could very well fix the problem, if we make the assumption that [JFF Type] could contain more content than what is being searched.

                 

                If [JFF Type] = "IT JFF, Project JFF, FY 2017"

                 

                Then…

                 

                CONTAINS ([JFF Type], "IT JFF, Project JFF") = TRUE

                but

                CONTAINS ("IT JFF, Project JFF", [JFF Type]) = FALSE

                • 5. Re: Multiple Else if in a calculated field
                  Jamieson Christian

                  Sameer,

                   

                  Others have already indicated, you can have as many ELSEIF blocks as you need. So something else is causing the remaining checks to fail.

                   

                  Here's a completely different way to approach the problem, to avoid having to create massive IF/ELSE statements:

                   

                  1. Create a Combined Field out of the 3 fields that you are checking.

                  2. Create a Group Field out of the Combined Field. You can create "groups of 1" to bucket everything separately (or lump stuff together if they get the same Workforce Category), and then rename the groups to whatever you want. It's kind of like Aliases, but for constructs that don't support editing aliases. (EDIT: Actually, you can specify aliases on a Combined Field — so you wouldn't need the Group Field — but you have to put the field onto the shelves before you can Edit Aliases. You can't do it from the Field List on the left.)

                   

                  Here's an example of creating a combined field and group for Segment and Category from the Superstore. I came up with group names, using the "Rename" button.

                  1 of 1 people found this helpful
                  • 6. Re: Multiple Else if in a calculated field
                    Sameer Mehrotra

                    Thanks Jamieson it worked perfectly fine and much easier than writing the formulas.

                    • 8. Re: Multiple Else if in a calculated field
                      Chris McClellan

                      Creating a group isn't the same as your original question though.  A group can't do the "contains" logic that you had in your initial.

                       

                      If the group works for you then great , but it's not what you were asking for at the start.

                      • 9. Re: Multiple Else if in a calculated field
                        Jamieson Christian

                        Sameer, glad it worked for you!

                         

                        Chris, you're absolutely right that the group doesn't behave exactly the same as the original IF block. I threw the group idea out there because I began to suspect that the CONTAINS() wasn't actually needed — that it was being used where a straight equality was sought. Worked out in this case.