3 Replies Latest reply on Jul 2, 2010 10:34 AM by Alex Blakemore

    If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??

    . Tzimmermann

      I am looking to create a Single-Value-List filter on 1 field [color] which has 3 values ('Red', 'White', 'Blue') but I want the filter to show 5 values: (1) Red, (2) White, 3) Blue, (4) Red/Blue, and (5) Red/White/Blue.

       

      This is the calculated field I have so far but it only gives me values #1-3.  How can I create/modify this to account for filters #4 & #5?

       

      IF [color]="Red" THEN "RED"

      ElseIF [color]="White" THEN "WHITE"

      ElseIF LEFT [color]="Blue" THEN "BLUE"

      end

        • 1. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
          Joe Mako

          Taking a data set that looks like:

           

           

          ID    Color
          
           01    Red
          02    White
          03    Blue
          04    Red
          05    White
          06    Blue
          07    Red
          08    White
          09    Blue
          10    Red
          


           

          you can use custom SQL like:

           

           

          SELECT [data#txt].[ID] AS [ID],

            [data#txt].[Color] AS [Color],

            [data#txt].[Color] AS [Filter]

          FROM [data#txt]

          UNION ALL

          SELECT [data#txt].[ID] AS [ID],

            [data#txt].[Color] AS [Color],

            "Red/Blue" AS [Filter]

          FROM [data#txt]

          WHERE [data#txt].[Color] = "Red"

            OR  [data#txt].[Color] = "Blue"

          UNION ALL

          SELECT [data#txt].[ID] AS [ID],

            [data#txt].[Color] AS [Color],

            "Red/White/Blue" AS [Filter]

          FROM [data#txt]

          WHERE [data#txt].[Color] = "Red"

            OR  [data#txt].[Color] = "White"

            OR  [data#txt].[Color] = "Blue"



           

          to shape the data, repeating rows to enable you to filter the way you want.

           

          When you show the quick filter, you will want to disable "All" from the customize menu, and change it to a single select filter, like in the attached workbook.

          • 2. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
            guest contributor

            hello joemako - I have been working with tzimmerman and we are both a bit overwhelmed by your sql code listed above since neither of us have experience with this code.  I am aware that we can embed your code into the data connection but we were hoping to siimply add to the formula below which we can use in a calcualted field.  Is there a way to simply add 1 or 2 lines to achieve our objective?

             

            Also, we are both currently using Tableau 5.1 so we can not access your attached workbook(which uses Tableau 5.2)

             

            IF [color]="Red" THEN "RED"

            ElseIF [color]="White" THEN "WHITE"

            ElseIF LEFT [color]="Blue" THEN "BLUE"

            end

            • 3. Re: If/Then/Else statement where first true <expr> returns MULTIPLE <then> values ??
              Alex Blakemore

              Joe's custom SQL solution is probably your best bet in this case as long as you take care to avoid double counting. For example, if you use a multi-select filter instead of single-select, and allow both "Red" and "Red/Blue" rows, you'll get double copies of each red row.

               

              You essentially want to use conditions to define your filter choices instead of using a simple list of values. One way to do that is to define calculated fields that you can use for your filter. That works well when you have one complex filter forumula that partitions rows into 2 or more categories. It's less easy to use when rows can belong to more than one category.

               

              For example, you can define [red_or_blue] as

              [color]="RED" or [color]="BLUE"
              . Then you can use [red_or_blue] to make a checkbox quick filter. The problem with this solution is that you can end up with a list of overlapping filters, and the user has to understand how they all interplay. If the [red_or_blue] filter is set to false and the [red_white_blue] filter is set to true, you'll only see white rows, but if they are both set to true, you won't see any white rows.