9 Replies Latest reply on Sep 26, 2019 3:48 AM by Abdul Rahman

    Use different background color for 3 data ranges in text table

    Xuequn Le

      Use different background color for 3 data ranges in text
      table

      Use different colors for different columns in text table;
      use different colors for different data range

      Is there some way, I can make the background color of
      columns “a” and “b” light blue, but use different background color (as follows)
      for different data range in column “a-b”:

      • 1-9  Orange
      • >=10 green
      • <=0 Red

      The result should be like this:

      ranged color.PNG

       

      Please find my current tableau file work attached.

      Thanks!

        • 1. Re: Use different background color for 3 data ranges in text table
          Michel Caissie

          Xuequn,

           

          You can create a Color  calculation

          if

          -SUM({FIXED [Store],[Month (Store)]:[b]})+ SUM({FIXED [Store],[Month (Store)]:[a]})

          >= 10 then 'abG'

          elseif -SUM({FIXED [Store],[Month (Store)]:[b]})+ SUM({FIXED [Store],[Month (Store)]:[a]}) <= 0 then 'abR'

          else 'abO'

          end

           

          Remove Measure Values from the color shelf  and drag both  Measure Names  and  this Color calculation on the color shelf (Ctrl-click, select both and drag)  .

          Set the colors accordingly.

          You may also want to change  the Mark  from Square to Bar , and add an axis on the Column. This way it will be easier to set the size of the cell without having squares overlapping.

          Use the Size shelf to set the height of the cell , and  use the Fixed values of the axis  to set the width of the cell.

           

          Michel

          • 2. Re: Use different background color for 3 data ranges in text table
            Xuequn Le

            Thanks Michel! Would you please attach your Tableau workbook for reference?

            • 3. Re: Use different background color for 3 data ranges in text table
              Xuequn Le

              Michel,

              I cannot open the your Demo(mc).twbx file. When I open it, this is my view:

              When I wasfollowing your steps,

               

              After the step of “Remove Measure Values from the color shelf and drag both Measure Names and  this Color calculation on the color shelf (Ctrl-click, select both and drag). Set the colors accordingly.” The color in the AGG(a-b) color legend changed. But this is the point I have big question with, because I cannot custom the color legend of (a-b) to that I desire, which is when the number is 1to 9, the background color is Orange; when the number is >=10, the background color is green; and when the number is <=0, the background color is red. And I do not see how the calculated color field helps here in the visualization result.

               

              In the step of “change the Mark from Square to Bar, and add an axis on the Column” I do not see how the bar helps the visualization goal, and want you to specify what axis to add on the column.

               

              Below is my current visualization result of trying your method, I think I need more details and your workbook to fill my understanding of the processes.

              • 4. Re: Use different background color for 3 data ranges in text table
                Michel Caissie

                Xuequn,

                 

                I attached  a 10.5 version.

                Here is also a screenshot

                 

                As you can see, both  Measure Names and Color  are on the color shelf.  In your screenshot you only have Measure Names.

                Click Color, then click CTRL and click Measure Names.  And then   Right-click  and drag on Color shelf.

                 

                Also to use Bar, you need an axis

                axis  is simply a calculation returning 100.

                In the example, I fixed the value from -5 to 105  to build little boxes,  but you could leave it to  0 to 100 if you want to color completely the width.

                And you can use the Size shelf, if you want to color the full height.

                1 of 1 people found this helpful
                • 5. Re: Use different background color for 3 data ranges in text table
                  Xuequn Le

                  In my result, the background color only covers half of the cell, and the numbers are on the left side of the cell (I want them to be in the middle).

                  Would you please help trouble shooting?

                  And how you open the Edit Axis window when the visualization is not a chart?

                  Thanks!

                  (Dummy data applied in the sheet)

                  • 6. Re: Use different background color for 3 data ranges in text table
                    Michel Caissie

                    Xuequn,

                     

                    -To show the axis,  right click the green pill  Axis  and select Show Header.

                    -To edit the axis,  right-click the axis and select  Edit axis.

                        -set values as in above screenshot  (or  0 to 100)

                    -Uncheck  Show Header to hide the axis.

                     

                    If you choose a Fix value other than 0 you will need to go in Format - Lines  and put none  at Zero Line.

                     

                    Regarding the Text placement, using this,  the Text will be centered but without  right justification.

                    If you want  centered Text with right justification, you will need a second axis.

                    One axis to color the bar, and one axis to align the Text.

                     

                    -Add another green pill Axis at the right of the first one.

                    -Click on the first one  and  drag  Measure Values from the Label shelf to the Detail shelf

                     

                    -Click the second axis and remove  both pills on the color shelf

                    -Temporary set the View  from Fit Width to Standard  to better see what`s going on.

                    -You can see on the Text axis that the Text is centered but   with  Left justification.

                    -Edit the axis and set to Reverse

                    -Fix the values to  0 to 225 . You can see that by setting the length of the bar that you can align the text.

                    -Click on color and set Opacity at 0% and border at none.

                    -Right-click the axis and set Dual axis

                    -Go back  to  Fit Width (as you can see you will need to adjust the axis depending on the width of the cell,  I changed it for  0 to 500)

                    -Finally,  right-click the green pill axis  and uncheck  Show Header

                    1 of 1 people found this helpful
                    • 7. Re: Use different background color for 3 data ranges in text table
                      Xuequn Le

                      Thanks, Michel!

                      I cannot open your 3.0.1(mc).twbx file......

                      • 9. Re: Use different background color for 3 data ranges in text table
                        Abdul Rahman

                        I must appreciate you for your detailed explanation.

                         

                        Thanks

                        Abdul Rahman