11 Replies Latest reply on Feb 22, 2015 5:37 PM by Leigh Fonseca

    Compute Color Range before Table Calculation Hiding Filter?

    James Morse

      Hi All - This issue may have already been solved on the forums, but I haven't found it.

       

      I am building a "My Portfolio" dashboard for my sales team that will automatically filter on the logged in user's portfolio of client accounts / subordinates.  I am showing sales performance versus target at 3 levels:

      1. Overall summary for the salesperson
      2. Breakdown by team member
      3. Breakdown by client

       

      For each, I am color-coding the variance from target.  However, for #1, the viz will only show 1 row, making the color static at the most positive color in the palette.  Instead of this, I want to color-code #1 based on the salesperson's variance relative to his/her peers.  To do, so I am computing the viz for the salesperson and his/her peers, and then using a table calc to hide the rows that are not the salesperson logged in.

       

      The problem is that Tableau's color computation still seems to base off of the final result set AFTER table calcs rather than beforehand.  Is there any way to get around this?

       

      I've attached a similar scenario using the Coffee Chain connection since I can't share my actual workbook.

        • 1. Re: Compute Color Range before Table Calculation Hiding Filter?
          Jason Scott

          Hello James,

           

          The reason this is happening is in the color swatch.  Right now you only have center defined at 0.  Now this will work when you have multiple items in you view, however when the is only one the range for start and end will automatically change to the range of the only displayed item.  in this case 0.058 for both start and end leaving only 1 color to display.

           

          I don't know how often this data changes, or if you have a set range you want the colors to band through but just by defining the end range to you max value will make your view work.  Play around with the start and end ranges and you should get what you desire.

           

          Jason

          • 2. Re: Compute Color Range before Table Calculation Hiding Filter?
            Matt Lutton

            This does make sense, although it is a bit confusing for sure.  The Table Calc filtering "trick" does not apply here.  That trick allows us to retain the values for each bar, but since only one is showing in the view, the legend has no way to know the full range of values for all markets.  Or, that is how it made sense inside my own brain just now...  but when I first looked at this, I thought: "That should work!"  -- Now I see that its not quite the same issue as, say, this: A Jedi (Filter and Table Calc) Trick | Tableau Software

             

            If you show all markets, then set the range of colors to be a fixed range, you can accomplish what you want--but then, your range is static.  I could not find a way to make it dynamic.

            Ranges.png

            Hope this helps a bit!

            1 of 1 people found this helpful
            • 3. Re: Compute Color Range before Table Calculation Hiding Filter?
              James Morse

              Thanks, Matthew and Jason.  Yeah, I considered hard-coding the ranges, but that forces me into grading in absolutes.  My workbook is actually dynamically merging recognized revenue with our forecasted/phased sales for booked orders and provides options for viewing future periods.

               

              Thus, using static ranges doesn't really make sense.  For example, if I wanted to see variance to target for the full year of 2014 today, every member of the dimension is going to be below, but that's not bad relative to where we are in the period.  To get away from absolutes, I was hoping to grade them against peers, but it seems like that isn't possible directly.

               

              It seems like I could base the color shelf on a percentile relative to peers though.  I may try this.  Just adding more complexity to an already complex dashboard!

              • 4. Re: Compute Color Range before Table Calculation Hiding Filter?
                Matt Lutton

                I would just try to think about this from the "color legend perspective" .  If your view is reduced to one Bar, the color range will never know the full range of values.  The Table Calc "LOOKUP" filter does not help in this regard.  I'm not sure there is a way to place a single pill on the color shelf to accomplish what you want--I will keep watching to see if anyone has a solution.  I certainly won't say it isn't possible, because most things are--I just cannot see how it would work.

                • 5. Re: Compute Color Range before Table Calculation Hiding Filter?
                  James Morse

                  I think I understand limitations of the color grading.  If I perform a table calculation partitioned by each member that force-ranks them (i.e. percentile or standard deviations away from the mean), then I can make an absolute static range along this calc that yields color relative to peers.  Am I missing something?

                  • 6. Re: Compute Color Range before Table Calculation Hiding Filter?
                    Matt Lutton

                    I have no idea; I just can't envision it myself.  Please do post back and show us if you are able!

                    • 7. Re: Compute Color Range before Table Calculation Hiding Filter?
                      Leigh Fonseca

                      I thought I'd check back on this post and see if anyone had found a solution to this.  Maybe Jonathan Drummey Shawn Wallwork or Joe Mako has an idea how to get the calculation to work?  I'm struggling with essentially the same issue.  I want the color range to be set by 5 members in the view but I only want to display one of them. I've tried hiding, using First()/Last() to display only the desired value and it always resets the range.

                       

                      For the project I'm working on the set of possible dimension members is very large (I'm using Category in the Superstore Sales dataset as an example) but the view will never compare more than 5 total, and the range for those could be VERY different from the set overall.

                       

                      I found this post just before posting the same question myself and am hoping someone may be able to shed some new light on it.  I put together a workbook where tab 2 shows the actual and desired outcomes. 

                       

                      Hoping to hear back some good news.  Happy Friday!
                      Leigh

                       

                      https://public.tableausoftware.com/static/images/Pr/PrimaryCategoryTest-Forums/2_ViewComps/1.png

                      • 8. Re: Compute Color Range before Table Calculation Hiding Filter?
                        Jonathan Drummey

                        Hi Leigh,

                         

                        Just found this notification in my spam filter. Are you still looking for help on this?

                         

                        Jonathan

                        • 9. Re: Compute Color Range before Table Calculation Hiding Filter?
                          Leigh Fonseca

                          Hi Jonathan,

                           

                          Thanks for following up.  Yes, I would still like to find a way to calculate a color range before applying a Tableau calculation.  I posted a workbook with an example use case.  One reason this is needed is the client would like the workbook to display the Primary Category (in this case Paper) on a worksheet where the dimension value is visible and you have a 40% change in sales.  Then on a separate worksheet display paper and a list of comps where the dimension values for the comps are aliased out.  So you'd see Paper, Comp A, Comp B, etc.  And in this instance you would see the YoY % change.  Both of these sheets would go on a dashboard.  This represents the first and third sheet that is displayed in my workbook. 

                           

                          Currently, because the color range is applied after the Tablea Calculation that does the filtering so I get view 2, which is the wrong color for the desired output.

                           

                          Any suggestions you have would be greatly appreciated.  Thank you in advance!


                          Leigh

                          • 10. Re: Compute Color Range before Table Calculation Hiding Filter?
                            Yuriy Fal

                            Hi Leigh,

                             

                            Looking at your wb, I would like to suggest

                            using a table calc for coloring when a filter applied.

                            Something like that in the attached wb.

                             

                            You may use the original color palette

                            as the only visible one on a dashboard.

                             

                            Hope this workaround may help.

                             

                            Yours,

                            Yuri

                            • 11. Re: Compute Color Range before Table Calculation Hiding Filter?
                              Leigh Fonseca

                              Thanks, Yuriy!  This is great and it seems to do the trick. I'll try this out with the production data set and see how it goes.  I really appreciate you helping me get this over the finish line.


                              Kind regards,

                              Leigh