1 2 Previous Next 15 Replies Latest reply on Apr 25, 2016 11:24 AM by Matt Lutton

    Always show a record regardless of filter

    Jon Boeckenstedt

      I use Tableau for some bench-marking and industry comparison data.  I've created a variable to use on the color shelf that colors my institution differently to help identify us in the group.  This is easy, and it works great when we want to compare ourselves to a group in which we are a member: All colleges in Illinois, for instance, or all universities over 20,000 in total enrollment.

       

      But sometimes, we want to compare ourselves to institutions outside our group: Big Ten colleges, or all publics, or Research I institutions.

       

      Is there a way to keep us on the same viz even when a filter selection would normally exclude us?  I've attached a mocked up workbook for demonstration purposes.

       

      Thanks.

        • 1. Re: Always show a record regardless of filter
          Ville Tyrväinen

          Hi,

           

          Is this kind of solution suitable for you?

           

          Ville

          example_.PNG.png

          2 of 2 people found this helpful
          • 2. Re: Always show a record regardless of filter
            Jon Boeckenstedt

            It works, but is there a way to do it without the tool tip always showing?

            • 3. Re: Always show a record regardless of filter
              Brittany Fong

              You can use the solution that Ville came up with.  You just have to reformat the annotation so that it doesn't show.  Right click on the DePaul mark and click edit.  Delete everything in the annotation.  Then right click on the DePaul mark again and click format.  Get rid of the border and line.  Technically the annotation will still be there but you won't see it!

              1 of 1 people found this helpful
              • 4. Re: Re: Always show a record regardless of filter
                Ville Tyrväinen

                With floating worksheet you can do something like this.

                 

                edit. now it's like it should be..

                • 5. Re: Always show a record regardless of filter
                  Jon Boeckenstedt

                  Thank you Ville and Brittany.  I like the first solution from Ville with Brittany's addendum.

                   

                  I'm a little embarrassed to ask how this was done?  I see an action was created, but that doesn't seem to explain it to me.

                   

                  Thanks again.

                  • 6. Re: Always show a record regardless of filter
                    Brittany Fong

                    So the point that is DePaul currently isn't actually a data point.  It's "hard keyed" where the "line end" has been changed to a dot and the size is as big as possible.  So if you click on the DePaul mark and move it, it moves around.  As long as you don't change what is being measured on the axis then it won't matter.  So annotate the mark and then change the end point to be a dot and line it up with the current dot.  I believe that's how Ville did it.

                    • 7. Re: Always show a record regardless of filter
                      Ville Tyrväinen

                      You guessed right Brittany

                       

                      p_1.PNG.pngp_2.PNG.pngp_3.PNG.pngp_4.PNG.pngp_5.PNG.pngp_6.PNG.png

                      • 8. Re: Always show a record regardless of filter
                        Jon Boeckenstedt

                        Thanks again.  I was busy and didn’t notice that it’s fixed on the axes.  I was hoping to be able to fix it on any view, with any combination of x- and y- axis.  (and sometimes I may want to do bar charts or other visualization types…this was just a demonstration viz.)

                        • 9. Re: Always show a record regardless of filter
                          Ville Tyrväinen

                          Hi Jon,

                           

                          I tested quite many different options and finally found couple solutions which might work for you. So let me know if you haven't already found solution by yourself.

                           

                          Br

                           

                          Ville

                          • 10. Re: Always show a record regardless of filter
                            Jon Boeckenstedt

                            I have not found a solution, and would be eager to see anything you can offer.

                             

                            Thanks very much for your time and attention to this.  I appreciate it.

                            • 11. Re: Re: Always show a record regardless of filter
                              Ville Tyrväinen

                              Hi Jon,

                               

                              Here is one alternative solution. Didn't have so much time to build anything extraordinary. There is some information behind INFO-button on the third dashboard.

                               

                              It would be nice to hear if there is easier or better way to do this. Of course you can always use multiple value filters...

                               

                              Br

                               

                              Ville

                              1 of 1 people found this helpful
                              • 12. Re: Re: Re: Always show a record regardless of filter
                                Jonathan Drummey

                                Hi,

                                 

                                Here's one way to go about this, using two reference lines to indicate where DePaul would be:

                                 

                                Screen Shot 2013-09-17 at 8.39.06 AM.PNG.png

                                 

                                There are a couple of ways to dynamically get the values for the reference lines. One would be to set up a custom query on the data source that returns the DePaul information for additional columns, another would be to use a duplicate secondary data source with a data source filter on DePaul in a Tableau data blend. I set up a version of the latter in the attached Benchmarking workbook because the data had already been extracted (note that the table calcs aren't actually necessary, more on them in a moment). This latter technique is a variation on what I covered in Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers.

                                 

                                There's another option that I came up with where I can have a mark show up for DePaul no matter the filter, but it requires using another school's mark - in other words, you have to be willing to throw away one data point to get DePaul to show up. If you're ok with that, let me know and I'll put the rest together. It uses some table calcs and a duplicated data source with the blend.

                                 

                                The real challenge here is that in the data set there are N marks (as determined by the dimensions in the view), and what we want is something more like N, or N-X, or N-X+1 marks, where X is the number of marks that are filtered out, plus 1 for DePaul when DePaul is filtered out. So, a third option is to set up a data source that can be filtered on whatever we want, while always returning a mark for DePaul.

                                 

                                My brain wouldn't let go of this, so I set up a proof of concept in the attached "depaul keep in filter workbook". The Custom SQL data source uses a subquery to return DePaul's information as extra columns in every row of the data source (which is what you'd do for the first option with reference lines that I proposed), then I unioned that data onto itself and added a dimension for Source that has two values: "DePaul Results" and "School Results".

                                 

                                What happens in Tableau is the following: Instead of just showing the School, Percent value, and Score value, there are calculated fields that look at the Source and return the DePaul values when the Source is DePaul Results, and the particular school values when the Source is School. This technique can be confusing at first, we're working with multiple levels of detail inside a calculated field - when the Source is a school, we'll return individual school results, when the Source is DePaul, we'll be returning only DePaul's results, and because we're using that same calculation inside the "School for Display" dimension, the view only has N+1 results.

                                 

                                Finally, because there can be two records for DePaul returned - one from the school Source, the second that is always present from the Depaul Source - I set up a "Duplicate DePaul Filter" calc that removes that extra DePaul mark. That lets us end up with desired N, N-X, or N-X+1 results.

                                 

                                A couple of improvements/optimizations that I can think of are:

                                - The subquery for the DePaul info could be use a parameter for picking the school, for alternatively be replaced by a self-blend for a dynamic selection of the comparison school.

                                - The Score for Display and Percent for Display calcs could be turned into aggregate calcs, that would improve performance on very large data sets.

                                 

                                I really like Ville's annotation option, that's a nice quick & dirty way to get a "mark" in the view. Beyond that, I'd probably go with the reshaped data, it has all the data and allows for almost any and all filtering. The one place where the filtering fails is when filtering on the measures being displayed, that can take the DePaul results out of the view. But in a view like this where the goal is to show the range of data, that seems like a reasonable compromise.

                                 

                                Jonathan

                                3 of 3 people found this helpful
                                • 13. Re: Re: Always show a record regardless of filter
                                  Jon Boeckenstedt

                                  Thanks to Jonathan and Ville for your really outstanding work on this problem.  I've taken a brief look at them, but won't get a chance to  dissect  them for a couple days to to scheduling.  But I want to let you know how much I appreciate your time and effort; I think I have a couple really good solutions that I can work with. 

                                   

                                  Once I get a look I'll come back and probably mark both of these as "correct answers."

                                  • 14. Re: Re: Always show a record regardless of filter
                                    Jon Boeckenstedt

                                    The power of this community and its collective willingness to share knowledge is beyond belief.

                                     

                                    I like Ville's solution very much, and the addition of the animation was a very nice touch. It's also do-able with my skill set, he said confidently while laughing nervously. 

                                     

                                    Of Jonathan's two solutions, I like the second better (no need to work on the first, but thanks) but it's beyond my technical skills, so I showed it to some of our more accomplished developers and it makes sense to them.  When we do this for larger, more important audiences like our Board of Trustees, it's likely to be the one we use.

                                     

                                    Anyway, thanks to both of you for two different but very good solutions to this problem.

                                    1 2 Previous Next