10 Replies Latest reply on Oct 5, 2016 7:49 AM by Joe Oppelt

    Coloring groups/totals

    Christopher Brand

      I am stuck on a tableau color formatting issue that I cannot solve.

      I need to color certain columns based on the values in those columns, I was able to to do this via calculated field using CASE and IF/ELSEIF. I also need to color other columns, sum them and color the totals.

      When I apply my coloring on the columns that are grouped to sum it breaks the sum and looks to only sum by color.

      Is there away that I can apply the color setup to my group and total without breaking the sum?

      I have a packaged workbook here:

      https://drive.google.com/file/d/0B8UbcGCskG_ac0d3NVUxRmliTkk/view

      The first sheet shows the sum working but no color, the second sheet shows colored columns and total but sum not working.

      Any help would be greatly appreciated.

        • 1. Re: Coloring groups/totals
          Joe Oppelt

          I'm looking at this.

           

          What SUM is not working?

          • 2. Re: Coloring groups/totals
            Christopher Brand

            Thank you for taking the time.

             

            The sum of the group works on the first sheet but if I un-remark out the coloring on the like I did on the second sheet the total breaks when the color changes.

             

            “Sheet 5”

             

             

             

             

             

             

            “No Color on totals”

             

             

             

             

             

             

             

            Let me know if this helps clarify the issue.

             

            Regards,

             

            Chris

            • 3. Re: Coloring groups/totals
              Joe Oppelt

              What I downloaded from the link you provided doesn't have window sums on it.

               

              I see you have a window_sum calc.  Where do you expect it to be placed, and what are the settings you have used for the table calc?

              • 4. Re: Coloring groups/totals
                Christopher Brand

                Sorry  - I am using the Totals  not sum on these sheets.  I was looking at window_sum  as potential solution that I could not get to work either.  This is the closest I got with the formatting and look that I need, just want to able to color the far right three columns based on value.  Perhaps the way the sheets are setup now will not allow for what I am attempting

                 

                CB

                • 5. Re: Coloring groups/totals
                  Joe Oppelt

                  Rebuild the workbook to reflect what you're actually describing.  there are no sums or totals anywhere, so I can't see examples of working/not-working.

                  • 6. Re: Coloring groups/totals
                    Christopher Brand

                    I think the issue you are seeing is that I change the  label on total to ‘blank’ so it does not appear.

                     

                    I have put the labels back and re-uploaded file.  Let me know if this help clarify.

                     

                     

                     

                     

                    • 7. Re: Coloring groups/totals
                      Christopher Brand

                      Thank you for your patience.

                       

                      Regards,

                       

                      Chris

                      • 8. Re: Coloring groups/totals
                        Joe Oppelt

                        I see what's happening now.

                         

                        So on your second sheet you are creating additional dimension values (more colors) in the EMAD calc.  When the colors are the same for both columns under FandP, you get one total value.  (See first three rows.)  But when you have two colors under FandP, the best Tableau can do is give you a total for each color.  (See rows 4-6.)  The TOTAL function is totaling by color.

                         

                        You're going to have to manage that total yourself, and display it strategically on a dashboard.

                         

                        Follow this progression in the attached.

                         

                        In Sheet 5 I modified the Window Sum calc and put it on the text shelf.  You can see you get a million copies of the value across the row.  But the value displayed is correct.  Notice that I did EDIT TABLE CALC on it, and ran it along specific dimensions, restarting every [PersonID].


                        Sheet 6 is a copy of that same sheet with the calc removed.  (I need the original sheet intact later.)

                         

                        Sheet 7 is a copy of sheet 6, but I dragged most of the dimensions onto the detail shelf.  (I need the dimensions to make the table calc behave.)  I have the table calc back on the sheet, and I also added an INDEX calc.  I need this index later.  Notice that I also ran INDEX along dimensions, and restarted every PersonID.  I still have multiple values, but that's what INDEX is for.

                         

                        Sheet 8 is a copy of 7, but I moved it up to the filter shelf, and selected for value = 1.  Now I get only one copy of the value per PersonID.

                         

                        Finally, I placed Sheet 6 and Sheet8 together on the dashboard.  I positioned Sheet 8 so that it floats over the column of Sheet 6 that we don't want to see.

                         

                        But we have one more problem.  (And this is a result of trying to get Tableau to look like Excel.)  Both those sheets scroll.  We don't have a scrolling synchronization mechanism that will make them scroll together.


                        So the upshot of all this is that I have showed you how to manage that sum.  But how to display it in a meaningful way still needs to be worked out.  Maybe we could display it within the cells of the two FandP columns.

                        • 9. Re: Coloring groups/totals
                          Christopher Brand

                          Joe,

                           

                          Thank you for the time on this.

                           

                          I had considered doing a dashboard to work around the issue but came to the same conclusion you did about having the asynchronous scroll issue.  In most cases when I am showing a limited data set it would be ok but we will have some users that will need to load either all the records or more than what will display in a non-scrolling size.

                           

                           

                          When you say display the totals with in the FandP columns can you elaborate on what you are thinking?  Would suggest a different approach to the display?  Part of the issue is the way I get the data and the other is that the end users are looking for something to reflect the excel sheet that that they have been using up until this point.

                           

                          Thank again for taking the time to take a look.

                           

                          CB

                          • 10. Re: Coloring groups/totals
                            Joe Oppelt

                            See attached.

                             

                            It's certainly not a complete solution as provided here.  But I threw the calc into TEXT, and it conditionally displays only for FandP.  (We would then hide the bogus column where multiple marks are coming out.)

                             

                            Some issues:  You can see that with the table calc in the mix on the TEXT shelf, now Tableau is selecting positions within the cell to place the mark.  (On all columns.)  I think we could play the same game with INDEX that I did on Sheet 8 so that everything displays in the first position, but I can't guarantee that will work.

                             

                            The root of the issue is the GROUP setup.  I'm wondering if the data could be re-shaped to have one dimension for the higher-level tests, and a second dimension to break apart FandP.  (The value of the second dimension for all the other tests would be blank.)  You can set colors across two dimensions if you need to.  I think this would all behave differently with a second dimension for sub-tests.