11 Replies Latest reply on May 26, 2016 10:01 AM by G Marc Turner

    How do I conditionally color my data using formula?

    Sameeksha Aithal

       

      The above table is based on the following condition:

      • If C is >= A+B, then green
      • If C is > 1/3 of A+B then yellow
      • If C is < 1/3 of A+B, then red

       

      Can I get it done by formula? If yes, how do I write the code?

       

      Is there any alternate way of displaying data, if not by formula?

        • 1. Re: How do I conditionally color my data using formula?
          Manideep Bhattacharyya

          Hello Sameeksha - Create a Calculated Field called KPI and use the formula below:

           

          if [C]<([A]+[B])/3 THEN "Red"

          elseif [C]>([A]+[B])/3 THEN "Yellow"

          elseif [C]>([A]+[B]) THEN "Green"

          END

           

          Drag this field in the Color Marks and set the color accordingly.

          • 2. Re: How do I conditionally color my data using formula?
            Sameeksha Aithal

            Hi Manideep,

            I am not able to fill the cell with the color after dragging it to color marks. Can you please help me out with it?

            I thought of highlight tables. I want "name" to be highlighted as well.

            • 3. Re: How do I conditionally color my data using formula?
              Manideep Bhattacharyya

              Hello Sameeksha - Provided screen shot. If you find this helpful then please mark as "Correct Answer" to close this chain

               

               

              KPI.png

               

              KPI Output.png

              • 4. Re: How do I conditionally color my data using formula?
                Manideep Bhattacharyya

                If this solve your problem, then please mark this as correct answer and close the thread.

                • 5. Re: How do I conditionally color my data using formula?
                  Sameeksha Aithal

                  Hi Manideep,

                  Can you do the same using highlighted tables?

                   

                   

                  Can you highlight the customer state (in this case) depending on the color priority (red - high, yellow - mid, green - low) ?

                  • 6. Re: How do I conditionally color my data using formula?
                    Sharad Joshi

                    Hi Sameeksha,

                    Can you attach the packaged workbook.

                    I want to see your data structure as well.

                     

                    Thanks

                    • 7. Re: How do I conditionally color my data using formula?
                      Manideep Bhattacharyya

                      KPI Formula.png

                      Change the KPI formula a little bit.

                       

                      Final Output.png

                       

                       

                      Then increase the size to look like a table. Hope this solves your problem.

                      • 8. Re: How do I conditionally color my data using formula?
                        Sameeksha Aithal

                        Hi Sharad,

                        The data is confidential. So, I can't share the data. Refer to the excel snapshot that I want to replicate in tableau. If you need any further clarifications, let me know.

                        • 9. Re: How do I conditionally color my data using formula?
                          G Marc Turner

                          I think the solution will depend in part on how your data is structure. If the data is structured like this:

                           

                          NameCategoryValue
                          AlphaA1(value)
                          AlphaB1(value)
                          AlphaC1(value)
                          AlphaA2(value)
                          AlphaB2(value)
                          AlphaC2(value)
                          etc

                           

                          the solution could be different than if the data is structured like this:

                           

                          NamePeriodABC
                          Alpha1(value)(value)(value)
                          Alpha2(value)(value)(value)
                          Alpha3(value)(value)(value)

                           

                          With the first data structure, I was able to get fairly close to your example using table calculations and a highlight table:

                           

                          example 1.jpg

                           

                          This solution also works if you break category into two fields, for example a Category (A, B, C) and a time (1, 2, 3). The key is that you have a single measure per row rather than 3 separate measures per row. The only thing I wasn't able to replicate was shading the NAME field based on the formatting of the C columns. I don't believe that Tableau allows you to format/shade the levels of dimension in the header separately. As a work around I added a "status" column to the beginning, assuming that the purpose of the color coding was to draw attention to those that needed attention. (I added the 4th NAME to have an example where all fields were green.) This solution does involve using table calculations to lookup the values in various columns, so it does require C to be every 3rd column. I've attached it as a packaged workbook in v9.3 (since that's the only version I have on this computer).

                           

                          If your data is structured in a different format, then a different solution will be required.

                           

                          -Marc

                          • 10. Re: How do I conditionally color my data using formula?
                            Sameeksha Aithal

                            Hi G,

                            The data can't be restructured that way. The data is not dependent. It is just a nomenclature that I followed. Have you modified the data that way to create a view like the one you showed here?
                            I am not able to open the attachment. So, can you please comment the code you wrote under table calculation?

                            • 11. Re: How do I conditionally color my data using formula?
                              G Marc Turner

                              To sort the categories, I used the following calculation to rearrange the numbers and letters into a new dimension:

                               

                              right([Category],1)+Left([Category],1)

                               

                              I then used the drop down for [Category] and sorted it by the dimension I just created. This allowed things to be sorted A1, B1, C1, A2, B2, C2, etc.

                               

                              To format the "C" columns, I used this formula:

                               

                              [Format Table Calculation]

                              if min(left([Category],1))="C" then

                                  if sum([Value]) >= zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) then "1-Green"

                                  elseif sum([Value]) >= ( zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) / 3) then "2-Yellow"

                                  elseif sum([Value]) < ( zn(lookup(sum([Value]),-2)) + zn(lookup(sum([Value]),-1)) / 3) then "3-Red"

                                  end

                              END

                               

                              This formula limits the calculations to the categories that start with "C". It then uses the LOOKUP() function to get the values of the previous 2 cells. You do need to make sure that it is computing along the [Category] dimension. I used this calculation with a highlight table to get set the colors of the different cells. The number was included at the beginning so I could use the MAX() function in the following formula to see the most severe:

                               

                              [Status]

                              case lookup(

                                  (Max(

                                      lookup(left([Format Table Calculation],1),2),

                                          Max(

                                              lookup(left([Format Table Calculation],1),5),

                                              lookup(left([Format Table Calculation],1),8)

                                          )

                                      )

                                  ) ,FIRST())

                                  when "1" then "GOOD"

                                  when "2" then "CAUTION"

                                  when "3" then "PROBLEM"

                              end

                               

                              Unfortunately this formula and approach only works with a fixed number of columns to lookup the values. It might be possible to create a calculation that determines the max across the row regardless of the number of columns using a table calculation.

                               

                              Hope this helps

                              -marc