11 Replies Latest reply on Sep 6, 2017 7:54 AM by Tzachi Zach

    Conditional formatting of cells in a Matrix

    Tzachi Zach

      In the attached workbook, there is a large matrix. The colors of the cells are a function of thresholds that i define in the dimension "measure for color". I love the colors. The middle color applies to values of "SL Winrate" that at are between 0.475 and 0.525. However, one exception should be the diagonal cells. Notice that the values of "SL Winrate" along the diagonal are all equal to 50% (by design). I would like to color these diagonal cells at a color that is outside the range (e.g. Black).

       

      Does anyone have any suggestions on how to do that?

       

      Thanks!

        • 1. Re: Conditional formatting of cells in a Matrix
          Michel Caissie

          Tzachi,

           

          Since you only use 9 colors of the color palette, I suggest you set your color dimension as discrete  and add a condition for the .500 value.

          Next, set each color according to your needs.

           

          see in the attached...

           

          I added a measure [axis]  on the Columns shelf. This is just a constant of value 1. If you Show Header and Edit axis, you will see that this axis is fixed from 0 to 1.

          And I changed the Marks from Box to Bar. This way the cell will be fully colored,  otherwise using a box with a discrete color the box would not cover the cell completely.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Conditional formatting of cells in a Matrix
            Tzachi Zach

            Michel:

             

            Thanks for your help on this.

             

            After your implementation, there is a slight distance (white space) between each cell. Is it possible to make the cells "stick" to each other under this approach?

             

            Also - is it possible to make the colors the same shades that i had them before?

             

            Thanks,

            Tzachi

            • 3. Re: Conditional formatting of cells in a Matrix
              Michel Caissie

              Tzachi,

               

              Regarding the size of the cell, you just have to click on the size shelf  and set the size of the bar at the max value.

               

              And regarding the colors, you will have to set manually the RGB values of each color.

               

              On your worksheet, select Edit colors from the color legend,  click on one color square to open the color selector, click on Pick Screen Color, hover over each color from the legend and note the RGB values.

               

              On the new worksheet, you can access the color selector by Edit colors, and double-clicking on each individual colors to open the color selector and set the RGB values.

               

              Michel

              1 of 1 people found this helpful
              • 4. Re: Conditional formatting of cells in a Matrix
                Tzachi Zach

                Hi Michel,

                 

                Thanks again for your response.

                 

                Regarding the color issue. I was not able to reach a selection to "pick screen color."    Maybe we have different versions? i have Tableau 10. In any case, i was able to find the RGB codes in anotehr way and i set them in the new file successfully, per your guidance.

                 

                As for the cell size issue, i am not sure where to find the "size shelf" nor could i find a way to set the bar at max. Can you help guide me there?

                 

                Thans,

                Tzachi

                • 5. Re: Conditional formatting of cells in a Matrix
                  Michel Caissie

                  Here are a couple of screen shots;

                   

                  For the colors

                  color2png.png

                   

                  color1png.png

                   

                   

                  Regarding the size

                   

                  size.png

                   

                  Also, I see that on my sheet some borders have been added. You can change the column divider lever to remove them.

                   

                  borders.png

                  • 6. Re: Conditional formatting of cells in a Matrix
                    Tzachi Zach

                    Thanks, That was perfect!

                     

                    As for the "pick screen color" - i was able to work it on my Windows machine. But, it seems that this option is not available on my Mac.

                    • 7. Re: Conditional formatting of cells in a Matrix
                      Tzachi Zach

                      I now stumbled on a related problem. When i change the data source, to a most updated file, all the color scheme breaks down. How do i make sure that the color scheme remains unchanged when i change the data source?

                      • 8. Re: Conditional formatting of cells in a Matrix
                        Michel Caissie

                        According to Tableau

                        Replacing a Data Source | Tableau Software

                        When replacing a data source, the following situations may occur:

                        • If the categorical colors on the Color shelf have been customized, when replacing the data source, the colors may revert to the automatic color settings defined by Tableau. The colors will need to be manually adjusted after replacing the data source.

                         

                        So, I guess you will have to reset the colors after changing the datasource.

                         

                        One good practice could be to connect to a generic filename, and when you get a most updated file, just rename it and replace the old one. This way you wont have to change the datasource in Tableau.

                         

                        Another workaround, when you have to change datasource and don't wont to redo all the customization is to directly edit the tableau XML file. So if you are comfortable with XML editing, you can open the .twb file (not the .twbx) in a XML editor and replace sections with the old values. Obviously this is not supported by Tableau and is at your own risk...

                         

                         

                         

                        <encoding attr='color' field='[none:Winrate for color (copy 2):ok]' type='palette'>

                                    <map to='#000000'>

                                      <bucket>100</bucket>

                                    </map>

                                    <map to='#24693d'>

                                      <bucket>4</bucket>

                                    </map>

                                    <map to='#398949'>

                                      <bucket>3</bucket>

                                    </map>

                                    <map to='#61a956'>

                                      <bucket>2</bucket>

                                    </map>

                                    <map to='#85ca77'>

                                      <bucket>1</bucket>

                                    </map>

                                    <map to='#ae123a'>

                                      <bucket>-4</bucket>

                                    </map>

                                    <map to='#da323f'>

                                      <bucket>-3</bucket>

                                    </map>

                                    <map to='#f36754'>

                                      <bucket>-2</bucket>

                                    </map>

                                    <map to='#fb9984'>

                                      <bucket>-1</bucket>

                                    </map>

                                    <map to='#ffc685'>

                                      <bucket>0</bucket>

                                    </map>

                                  </encoding>

                                  <encoding attr='color' field='[usr:Winrate for color (copy 2):ok]' type='palette'>

                                    <map to='#000000'>

                                      <bucket>100</bucket>

                                    </map>

                                    <map to='#24693d'>

                                      <bucket>4</bucket>

                                    </map>

                                    <map to='#398949'>

                                      <bucket>3</bucket>

                                    </map>

                                    <map to='#61a956'>

                                      <bucket>2</bucket>

                                    </map>

                                    <map to='#85ca77'>

                                      <bucket>1</bucket>

                                    </map>

                                    <map to='#ae123a'>

                                      <bucket>-4</bucket>

                                    </map>

                                    <map to='#da323f'>

                                      <bucket>-3</bucket>

                                    </map>

                                    <map to='#f36754'>

                                      <bucket>-2</bucket>

                                    </map>

                                    <map to='#fb9984'>

                                      <bucket>-1</bucket>

                                    </map>

                                    <map to='#ffc685'>

                                      <bucket>0</bucket>

                                    </map>

                                  </encoding>

                        • 9. Re: Conditional formatting of cells in a Matrix
                          Tzachi Zach

                          Michel:

                           

                          Thanks for your help with this issue last year. I have a follow-up question related to the same issue but with a different file. In the attached file, there are four sheets (Table 3 - Q1 through  Q4), in which I would like to achieve a similar goal. I prepared the "color" variables with values -1 to 3. However, i am again having trouble with sizing. Ideally, i would like the view to be similar to the one in Table 3- Q2. Accordingly, i have resized the cells. However, when i hover the mouse to see the tooltip, it seems that the cells are "too big." So, the sizing does not seem to work well in this file, as it did in the file last year.

                           

                           

                          Another issue is that in Table 3 - Q1, i am unable to bring the "color legend" so that i can easily change the colors of each level.

                           

                          Thanks for your help.

                           

                          Tzachi

                          • 10. Re: Conditional formatting of cells in a Matrix
                            Michel Caissie

                            Tzachi,

                             

                            First, regarding the color legend,  go in the menu Worksheet - Show Cards - Reset Cards  to display the color legend. You probably hidden the card at some point. This will also remove your parameter and quick filter and you will have to bring them back.

                             

                            Regarding the size, what you need is to add an axis and use a bar mark instead of a box, like in the previous post. Edit the axis to have a fixed range from 0 to 1 , then unselect Show Header to remove the axis.

                             

                            Michel

                            1 of 1 people found this helpful
                            • 11. Re: Conditional formatting of cells in a Matrix
                              Tzachi Zach

                              Michel:

                               

                              Thanks again. This was very helpful. Need to internalize the usefulness of the axis trick.

                               

                              Tzachi