1 2 Previous Next 19 Replies Latest reply on Mar 7, 2018 10:55 AM by Christiaan Casilimas

    Color Coding Cells per Conditions

    Eric Horio

      I need help figuring out how to color code certain cells per multiple conditions.

       

      I've attached a sample workbook.

       

      This would apply only to the "Pipe Created" measure. 1) I would like any cell with a value of "Stage 1" and is >=2000 to be colored green. If it's <= 1999, then leave the cell alone. 2) For a value of "Stage 2" and anything that is >= 1500, then make that cell green, otherwise leave it alone. 3) For a value of "Stage 3" and anything that is >= 1000, then make that cell green, otherwise leave it alone.

       

      Any assistance is appreciated!

        • 1. Re: Color Coding Cells per Conditions
          Adam Crahen

          Hey Eric-

           

          Give this a look and see if it works.

          2016-02-23_21-11-56.png

           

          I used MIN(Number of Records) on Size, mark type bar with size dialed all the way up.

           

          Color

          IF ATTR([Stage]) = 1

              THEN IF SUM([Pipe Created]) >= 2000

              OR SUM([Pipe Open]) >= 2000

              OR SUM([Pipe Won]) >= 2000

              OR SUM([Pipe Lost]) >= 2000

                  THEN "Green" ELSE "White" END

           

          ELSEIF ATTR([Stage]) = 2

              THEN IF SUM([Pipe Created]) >= 1500

              OR SUM([Pipe Open]) >= 1500

              OR SUM([Pipe Won]) >= 1500

              OR SUM([Pipe Lost]) >= 1500

                  THEN "Green" ELSE "White" END

           

          ELSEIF ATTR([Stage]) = 3

              THEN IF SUM([Pipe Created]) >= 1000

              OR SUM([Pipe Open]) >= 1000

              OR SUM([Pipe Won]) >= 1000

              OR SUM([Pipe Lost]) >= 1000

                  THEN "Green" ELSE "White" END

           

          ELSE "White"

           

          END

           

          9.2 workbook attached.

          • 2. Re: Color Coding Cells per Conditions
            Eric Horio

            Thanks Adam. How do I get the conditional formatting to only apply to the Pipe Created column? I would like to leave the other columns alone.

             

            How does the "Min ( Number of Records )" affect the coloring?

            • 3. Re: Color Coding Cells per Conditions
              Adam Crahen

              Oh whoops, you did say just pipe created.

               

              If you just want the one column colored, you have to use a trick to turn your dimensions into measures and then label the mark with the actual value.

               

              You have to remove measure names from the columns and filter.  Then you are going to put four copies of MIN(Number of Records) in the column shelf

              2016-02-23_23-16-23.png

              Now you will see 5 marks cards.  All and one for each of the Min(Number of Records) in your column shelf.

               

              2016-02-23_23-17-50.png

               

              What this is going to do is draw every mark on the number one.  This is what keeps the text all in straight column.  Then your actual Measure goes on label.  I am only using the color formula on the Pipe Created Marks Card.

               

              The Other MIN(Number of Records) on size is what draws a bar behind the text of equal sizes throughout.  Dial size all the way up.

               

              I simplified the color formula since you are only after pipe created.

               

              Color

              IF ATTR([Stage]) = 1 AND SUM([Pipe Created]) >= 2000

                  OR ATTR([Stage]) = 2 AND SUM([Pipe Created]) >= 1500

                      OR ATTR([Stage]) = 3 AND SUM([Pipe Created]) >= 1000

                      THEN "Green" END

               

              The next step is to edit the axes for each MIN(Number of Records).  Edit the axis title to your measure name, fix the axis to start at 0 and end at 1, and remove all tick marks.

               

              If you want to have the title at the top of the column, you need to add eight copies of MIN(Number of Records) to your column shelf.  On the second pill create a dual axis.  The 1st and 2nd marks cards should be identical.  You would edit the top axis like I just described and then blank out the title and tick marks on the lower axis.  Repeat this dual axis step on the 4th, 6th and 8th pills.

               

              2016-02-23_23-29-12.png

              9.2 attached again.

              3 of 3 people found this helpful
              • 4. Re: Color Coding Cells per Conditions
                Eric Horio

                Thanks Adam, that was really complex! Is that the only way to do it? I'm thinking it might clutter my worksheet, especially if I need to add additional dimensions to it

                 

                Let's say I wanted to just highlight the number to green, instead of the cell. Is that process easier? How would I do that?

                • 5. Re: Color Coding Cells per Conditions
                  Nikunj Bhardava

                  Hi Eric,

                   

                  One of the approach that @adam suggested will work fine but will create complex view. instead you could achieve same by changing your data. i.e changing your measures(pipe open, close, won and lost) into dimension like (category).

                   

                  Thanks, Nikunj

                  • 6. Re: Color Coding Cells per Conditions
                    Adam Crahen

                    Hi Eric-

                     

                    You can add additional dimensions to the row shelf without any complications in that solution.

                     

                    The process is no different for coloring text vs. the cell behind.  The problem is that the color will applied to the entire dimension when a condition is met.  Because you have multiple measures in this view, you were using measure names as your column dimension.  We cannot reference Measure Names in a calculated field.  That is why when the condition was met that pipe created was over 2000 then it colored all cells for Willie across Measure Values.

                     

                    2016-02-24_08-15-58.png

                     

                    As Nikunj suggested, you could reshape your data.  If your real data source is excel or text file, you can pivot directly in Tableau.

                    Pivot Data (from Columns to Rows)

                     

                    However, your measures are calculated fields so this won't work unless you do those calculations in Excel and then pivot in Tableau.

                     

                    I did that for you and attached the file below.  Now you can connect to this source and pivot these fields.

                    2016-02-24_08-25-41.png

                    Now you have two columns for pivot field names and pivot fields values, you can rename these if you want.

                     

                    2016-02-24_08-27-14.png

                     

                    Now we can reference the pivot field names in the calculation because it is a real dimension and can achieve what you want.

                     

                    Color

                    IF ATTR([Pivot field names]) = "Pipe Created Excel"

                        THEN IF ATTR([Stage]) = 1 AND SUM([Pivot field values]) >= 2000

                        OR ATTR([Stage]) = 2 AND SUM([Pivot field values]) >= 1500

                        OR ATTR([Stage]) = 3 AND SUM([Pivot field values]) >= 1000

                            THEN "Green" END END

                     

                    2016-02-24_08-37-49.png

                    I made a copy of this and did it as just text too.

                     

                    Excel file and 9.2 workbook attached.  Either way is a little bit of work.

                    • 7. Re: Color Coding Cells per Conditions
                      Eric Horio

                      Thanks Adam.

                       

                      Can either of you show me the answer where I can convert the measure to a dimension and show the highlighted cells? I would like the week ending and the category ( i.e. Pipe Created) at the top.

                      • 8. Re: Color Coding Cells per Conditions
                        Adam Crahen

                        Hi Eric-

                         

                        Follow the pivot example above.  Converts the measure to dimension and pipe created at the top.

                        • 9. Re: Color Coding Cells per Conditions
                          Eric Horio

                          My data is coming from SQL Server, so I can't do any calculations to the data that's feeding Tableau. Will I still be able to do the pivots example you provided? I noticed you added calculations to the Excel data file.

                          • 10. Re: Color Coding Cells per Conditions
                            Adam Crahen

                            No. I mentioned that the pivot only works on Excel or text files and you can't pivot the calculated fields.  Your Pipe fields are calculated fields.

                             

                            You would need to reshape the data on your SQL server if you wanted to achieve what you are trying to do.

                             

                            Or you can use the first solution I gave you with the Multiple marks cards.

                            • 11. Re: Color Coding Cells per Conditions
                              Nikunj Bhardava

                              Ohh adam, if your data is coming from the sql server then it will be easy to build query or storedd procedure that generates appopriate data. i.e you could do pivoting in sql server itself. then you only need to pull that data to tableau. quite easy

                              • 12. Re: Color Coding Cells per Conditions
                                Eric Horio

                                If I added Subtotals to this , how could I prevent the subtotals from being highlighted to green? See Stage 3 Total, under Pipe Created = 1700

                                • 13. Re: Color Coding Cells per Conditions
                                  Adam Crahen

                                  Hey Eric-

                                   

                                  Modify the Color formula.  Looks like just one record per mark at this detail level.  So if the record is greater than 1 it is a total.

                                   

                                   

                                  Color

                                  IF SUM([Number of Records]) = 1

                                      THEN IF ATTR([Stage]) = 1 AND SUM([Pipe Created]) >= 2000

                                      OR ATTR([Stage]) = 2 AND SUM([Pipe Created]) >= 1500

                                          OR ATTR([Stage]) = 3 AND SUM([Pipe Created]) >= 1000

                                          THEN "Green" END END

                                   

                                  9.2 workbook attached.

                                  2 of 2 people found this helpful
                                  • 14. Re: Color Coding Cells per Conditions
                                    pooja.gandhi

                                    That was a niceee trick. I mostly use sum(measure value) != total(sum(measure value). But that wouldn't work when you have only 1 mark and the sub total in that case would equal the mark itself. Great trick

                                    1 2 Previous Next