10 Replies Latest reply on Dec 8, 2017 6:44 AM by Norbert Maijoor

    How to Conditionally select color based on hierarchy

    Joe Morris

      I can't really share the workbook so ill try and explain this as best i can.

       

      So I have a text visual with the hierarchy:

      Warehouse - Market - Carrier - Service.

      As the row values.

       

      And a text mark that displays a percentage.

       

      If the percentage is less than 91 its red, 91 to 95 its orange & 95+ its green.

       

      Now the difficulty is if a carrier has 3 services in it, and they have percents 97, 98, 87 (green, green, red)

      When you roll the hierarchy up to the carrier level it averages those 3 percents and shows 94 (orange)

       

      The desired outcome would be for the carrier to show the color of its worst service, so it would show 94 and be colored red.

       

      --------------------------------------------------------

       

      To try and solve this I wrote a thing to check if the hierarchy was expanded.

       

      IF { EXCLUDE [Warehouse]: MIN([Market]) != MAX([Market]) } THEN 'Warehouse'
      
      ELSEIF { EXCLUDE [Warehouse],[Market]: MIN([Carrier]) != MAX([Carrier]) } THEN 'Market'
         
      ELSEIF { EXCLUDE [Warehouse],[Market],[Carrier]: MIN([Service]) != MAX([Service]) } THEN 'Carrier'
      
      ELSE 'Service'
      END
      

       

       

      Now i need to replace the strings in the above code with calculations to pick the color.

       

      IF  { EXCLUDE  [Warehouse]: MIN([Market]) != MAX([Market]) } THEN 
      
              IF { FIXED [Service], [Carrier], [Market], [Warehouse]: [SLA Performance] >= MIN([Service Level Target]) } THEN 'Green'
              ELSEIF { FIXED [Service], [Carrier], [Market], [Warehouse]: [SLA Performance] <= (MIN([Service Level Target])-6) } THEN 'Red'
              ELSE 'Amber'
              END
      
      ELSEIF  { EXCLUDE  [Warehouse],[Market]: MIN([Carrier]) != MAX([Carrier]) } THEN 
      
              IF { FIXED [Service], [Carrier], [Market]: [SLA Performance] >= MIN([Service Level Target]) } THEN 'Green'
              ELSEIF { FIXED [Service], [Carrier], [Market]: [SLA Performance] <= (MIN([Service Level Target])-6) } THEN 'Red'
              ELSE 'Amber'
              END
              
      ELSEIF  { EXCLUDE  [Warehouse],[Market],[Carrier]: MIN([Service]) != MAX([Service]) } THEN 
      
              IF { FIXED [Service], [Carrier]: [SLA Performance] >= MIN([Service Level Target]) } THEN 'Green'
              ELSEIF { FIXED [Service], [Carrier]: [SLA Performance] <= (MIN([Service Level Target])-6) } THEN 'Red'
              ELSE 'Amber'
              END
      
      ELSE 
              IF { FIXED [Service]: [SLA Performance] >= MIN([Service Level Target]) } THEN 'Green'
              ELSEIF { FIXED [Service]: [SLA Performance] <= (MIN([Service Level Target])-6) } THEN 'Red'
              ELSE 'Amber'
              END
      END
      

       

      Where service level target is a fixed value of 95 & SLA performance is the percentage.

       

      The bit that checks if the hierarchy is expanded works fine, but the code inside that to check the colors is inconsistent, any ideas?

       

      Thanks

      Joe.

        • 1. Re: How to Conditionally select color based on hierarchy
          Norbert Maijoor

          Hi Joe,

           

          Not sure but find my attempt as reference below and stored in attached workbook version 10.3 located in the original thread

           

           

          1. M1. Avg: {Fixed [Warehouse],[Market],[Carrier]:avg([Percentage])}

           

          2. D1. Intermediate Colour Indicator:

          If [Percentage]<=0.91 then "Red"

          elseif [Percentage]>0.91 and [Percentage]<=0.95 then "Orange"

          elseif [Percentage]>0.95   then "Green"

          end

           

          3. D2. Colour Indicator: {fixed [Warehouse]:max([D1. Intermediate Colour Indicator])}

           

          4. Drag required objects to the indicated locations

           

           

          Regards,

          Norbert

          • 2. Re: How to Conditionally select color based on hierarchy
            Joe Morris

            Apologies, I'm running tableau 9.3 so i can't actually open the workbook you sent.

             

            But from the look of the screenshots using warehouse A as an example :

             

            The percentage for S1 & S2 should be green and S3 should be red.

             

            And when rolling up to C1 you should have a value of 94 displayed as red, I don't think that's the result you ended up with.

             

             

            -----------------

             

            Here is an example of it working as intended:

             

            Belgium have 4 services ranging from green to red

            When you roll this up to a carrier level, they average 99.2 which would be shaded green, but as the lowest service used to take that average was 85.3 (red) the 99.2 is shaded red.

             

            • 3. Re: How to Conditionally select color based on hierarchy
              Norbert Maijoor

              Hi Joe,

               

              Find my updated approach below and stored in attached workbook version 9.3 located in the original thread.

               

               

               

              1. M1. Colour Indicator:

               

              if {exclude [Warehouse]:min([Percentage])}<0.91 then "Red"

              elseif {exclude[Warehouse]:min([Percentage])}>=0.91 and {exclude[Warehouse]:min([Percentage])}<=0.95 then "Orange"

              elseif {exclude [Warehouse]:min([Percentage])}>0.95 then "Green"

              END

               

               

              Regards,

              Norbert

              1 of 1 people found this helpful
              • 4. Re: How to Conditionally select color based on hierarchy
                Joe Morris

                Sorry, This was helpful and got me closer to an answer but there is still an issue,

                 

                 

                I took your example and added my measures to get this:

                 

                 

                IF {exclude [Warehouse]:[SLA Performance]} >= [Service Level Target]  THEN 'Green'

                ELSEIF {exclude [Warehouse]:[SLA Performance]} <= ([Service Level Target]-6)  THEN 'Red'

                ELSE 'Amber'

                END

                 

                 

                The problem is, I cant use Min(SLA Performance) in the same way you used Min(Percentage) as it is already an aggregate. It still works fairly consistently without, but there is a few incorrect cells also everything on a warehouse level is displaying orange instead of red

                • 5. Re: How to Conditionally select color based on hierarchy
                  Norbert Maijoor

                  Hi Joe,

                   

                  Could you share a workbook with a dummy-dataset to get a "full-picture" of your data-structure?

                   

                  Regards,

                  Norbert

                  • 7. Re: How to Conditionally select color based on hierarchy
                    suresh.gooty

                    I am not sure why you are not able to write a formula based on percentage and put that formula in color mark as below.....

                    if %>92 and %<94 then

                    Red

                    elseif %>=94 and %<96 then

                    green

                    elseif %>=96 and %<98 then

                    Blue

                    else

                    Orange

                    end...... did you tried this approach? try once... normally if you give color name it automatically search for that color and apply that color in tableau..... it is similar for all mark types...... hope this helps

                    • 8. Re: How to Conditionally select color based on hierarchy
                      Norbert Maijoor

                      Hi Joe,

                       

                      Find my adjusted approach below and stored in attached workbook version 9.3 located in the  original thread

                       

                       

                       

                      1. M1. Service Level Target: {exclude   [Warehouse],[Market],[Carrier]:avg([Service Level Target])}/100

                       

                      2. M2. SLA Performance: ({exclude [Warehouse],[Market]:avg([Parcel Count])}-{exclude [Warehouse],[Market]:avg([Late Parcel Count])})/{exclude [Warehouse],[Market]:avg([Parcel Count])}

                       

                      3. M3. Volume / Performance: if [Data Type]="Percentage" then mid(str([M2. SLA Performance]*100),1,4)+" %"

                                                                      elseif [Data Type]="Actual" Then str([Parcel Count])

                                                                      end

                       

                      4. M4. Colour Indicator: If min({fixed  [Warehouse],[Market],[Carrier],[Service]:min([M2. SLA Performance])})>=sum([M1. Service Level Target]) then "Green"

                                                            elseif min({fixed  [Warehouse],[Market],[Carrier],[Service]:min([M2. SLA Performance])})>=sum([M1. Service Level Target]-0.06) then "Amber"

                                                            else "Red" END

                       

                      5. Drag required objects to the indicated locations and show parameter control

                       

                       

                      Regards,

                      Norbert

                      1 of 1 people found this helpful
                      • 9. Re: How to Conditionally select color based on hierarchy
                        Joe Morris

                        That's fixed it,

                         

                        Thanks for committing so much time to this, this has been a thorn in my side for weeks.

                         

                        Appreciate it.

                         

                        Joe.

                        • 10. Re: How to Conditionally select color based on hierarchy
                          Norbert Maijoor

                          Hi Joe,

                           

                          "Love it when a plan comes together";)

                          Thanks for reaching out to the community and the "Badge". Appreciated:))

                           

                          Have a nice weekend.

                           

                          Regards,

                          Norbert