6 Replies Latest reply on Mar 31, 2017 1:30 PM by Simon Runc

    The "Color as a filter" request, only a bit more involved

    Joshua Holt

      Hello all,

          I am trying to create a filter that is based on categories I have created. The advice I have seen (e.g. this) in most places usually ends up coming back to this sort of approach which I cannot use because my categories rest on an aggregated measure themselves, so we have turtles all the way down (kind of). The solution that I would like to use is to create a clickable filter that rests on top of selecting ranges. This seems sort of impossible. I have attached a work book that has a very similar problem using the super store data. I have tried a few approaches, such as creating a dimension that kind of sits on top, but this just removed all my results. I look forward to any suggestions.

        • 2. Re: The "Color as a filter" request, only a bit more involved
          Simon Runc

          hi Joshua,

           

          I'm sure it's me, but I don't understand the problem...

           

          Can you not just use your Ratio (2015) (colors) on the filter shelf? or is it that you want this classification (as in Ratio (2015) (colors) in a sheet, without needing customer in the Viz for it to calculate correctly? If option 2, then FIXED LoDs are the way to go.

           

          If you can let me know I'll take a look.

          • 3. Re: The "Color as a filter" request, only a bit more involved
            Joshua Holt

            Hi Simon,

                I know that what I am about to say may come off a bit whine-y, but my target audience (and boss) are sales people. The only issue with your direct and simple solution to this problem is that you don't see the colors in the thing you are clicking on. To quote my guidance on this "I want to be able to click on the colored square and see just those". So I have tried making dimensions that are the same as the measure " Ratio (2015) (colors)" and using that, this gets me very close, as I can see the color in the filtering object (I make the dimension into a table), but when I try to link it to the graph everything ends up disappearing.

             

            Thank you for your time on this!

            • 4. Re: The "Color as a filter" request, only a bit more involved
              Simon Runc

              hi Joshua,

               

              ah for the boss!!

               

              So if I've understood correctly, this should do the trick...

               

              I've changed the Sales 2015 Ratio to an LoD. This means it gets calculated at the defined level (Customer Name in this case), but the results are returned at row level (at the Customer Name level), like a real dimension (I refer to these as Off-Canvas calculations, as the results are independent of the Viz LoD).

               

              So the [Ratio 2015 - LoD] is

               

              {FIXED [Customer Name]: SUM(

              IF year([Ship Date])=[2015]

              THEN [Sales]

              END)}

               

               

              /

               

               

              {FIXED [Customer Name]: SUM(

              IF YEAR([Ship Date])=[2015]

              THEN [Profit]

              END)}

              *

              100

               

              and I can then replace the Ratio LoD colour field, with that new measure

               

              [Ratio (2015) (colors) - LoD]

              IF ([Ratio (2015) - LoD])<= 85

                  THEN 'Less than 85'

              ELSEIF ([Ratio (2015) - LoD]) > 85 AND ([Ratio (2015) - LoD]) <=10000

                  THEN 'Between 85 and 10000'

              ELSEIF ([Ratio (2015) - LoD]) > 10000

                  THEN 'Greater than 10000'

               

               

              ELSEIF ISNULL([Ratio (2015) - LoD])

                  THEN ''

              END

               

              Notice how this field can now sit in the dimensions pane.

               

              Once we have this, I can create a separate sheet, which acts as my filter. I've only set it up as a highlight action, so you can see that it does the same on both Vizes (using your original with the Aggregated viz LoD dependent version of the classification)

               

              Hope that is what you were after, and makes sense, but let me know if not

              1 of 1 people found this helpful
              • 5. Re: The "Color as a filter" request, only a bit more involved
                Joshua Holt

                This worked brilliantly, thank you for your help.

                 

                If you will indulge me one more quick question: do I understand correctly that Tableau requires things to be static in order to be a dimension?

                • 6. Re: The "Color as a filter" request, only a bit more involved
                  Simon Runc

                  Excellent news!

                   

                  So yes static in the way that the result doesn't depend on the Level of Detail in the canvas (the vizLoD), or to put it another way not using an aggregation (such as SUM/AVG/MIN...etc.). If you think about SUM([Sales]), when you bring it on the canvas (with no dimensions at all), it gives you the SUM of Sales for everything (one value), if you then bring region onto the canvas (say on rows), there are now 3 values, which is the SUM of Sales for each region, as you'd expect. So any calculations that depend on SUM of Sales are calculated differently, depending on the VizLoD. As Tableau doesn't know that you don't want your calculation evaluated at Customer Name level, it can't create the result, until you bring customer name onto the Viz, so Tabelau knows at what level you wanted it calculated at (of course it does execute the result, just not how you want!). FIXED LoD, let us tell Tableau the level at which we want it calculated without needing that level on the canvas, so can create a "real" dimension.

                   

                  You may find this link useful...Answer - Quora

                  2 of 2 people found this helpful