1 2 Previous Next 18 Replies Latest reply on Jun 21, 2017 2:29 PM by Jason Freeman

    Creating a dynamic "top-n" filter on a stacked bar graph???

    Nicholas Smith

      I am fairly new to Tableau and have spinning my wheels on this one for a couple days now.  The threads that I've come across have shown how to do this in in text tables, but I can't seem to figure out how to make it work on the stacked bar graph.  I would be eternally grateful if anyone has a workaround they could share

       

      Data: Audit findings and ratings by 1. Audit Title, 2. Function and 3. Process.

       

      Goals:

      - Create a three window dashboard (see attached) and eliminate the need for a scroll bar in all windows by using a top n filter on total # of findings.

      - Use dynamic stacked bar graphs showing total # of findings, but also the # of findings for each risk-rating color (see attached). 

      - All tables in the dashboard will be filterable.

       

      Methods Attempted:

      - Top N Filtering.

      - Top N Filtering on ranked fixed subtotal fields.  Table function for ranked and creating table filter for top 3 or top 5.

      - Some experimenting with include/exclude functions to remediate issues experienced on above.

       

      Issues Experienced:

      - No matter how I cut it, or how I run the calculation, tableau seems to be dropping findings from view.  For example, when I click into a specific "Function" the total findings that populate in the "Process" and "Audit Title" views does not reconcile to the total findings filtered.  Note that this is not in the dashboard example uploaded, as this does not contain the top-n filters I have been experimenting with.  I can upload that version too if it would be helpful, but didn't want to provide too much info (although I am sure I already have!)

       

      Please note:  Dummy data attached doesn't include our full portfolio of "Functions" and, as such, does not show a scroll bar.  Ultimately, I would like to limit this window to the top 4 Functions dynamically based on total finding count.  

        • 1. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
          Christina Gremore

          Hi Nicholas,

           

          Could you tell us a little bit more about the desired result? For instance, if I filtered the Dashboard in your Dummy Reporting twbx to "Top 3", what 3 items would show on each sheet in the dashboard?

          • 2. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
            Paul Chaney

            Hello, Nicholas.  Glad to see a follow member of the audit profession in here!  Not sure if I'll have time to dive into this before Christina or someone else helps you figure it out, but I wanted to pass along something that might help a ton.  I'm relatively new to Tableau, as well, and watching the tutorial videos have been very helpful.  Your question immediately made me think of a tutorial that might help (Filtering for Top and Top N | Tableau Software ).  If I remember correctly, they give some good technical background that might find root causes.  Other tutorial vids, like those on Dashboards and Stories, helped me learn how to get rid of scroll bars on dashboards and visually show what I wanted to show for the best user experience.

             

             

            Paul

            • 3. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
              Nicholas Smith

              Yes, of course.  Thanks for taking a look at the workbook!  I think I can best answer that in two levels:

               

              FIRST: Unfiltered at the dashboard level:

              - Function - Top three functions by TOTAL finding count (ie -  Finance, HR, and Technology)

              - Process -  Top 4-5 Process/Mega-Process  Two points here:

                  - The mega/process distinction is not entirely important here.  In retrospect, I probably should have left that out of the view.

                  - The window would ideally show the top processes within the Functions that are visible in the function filter.  Ie - unfiltered, it would show the top processes in the Finance, HR, and Technology functions.

              - Audit Reports - Same as process - showing the top audits within the functions that are visible.

               

              SECOND: Dashboard filtering. - User would be able to click into a specific function, process or audit and the views would filter accordingly. 

              - For example, clicking into the "Finance" function would show you the top finance processes and the audits with the most finance findings.

              -For another example, clicking into the 10 Yellow Finance Findings in the "Function" window would show you 1. All processes where those yellow findings were situated. and 2. All audits the findings came from.

               

              I realize that the above is REALLY wordy, and kind of thick, so please let me know if I can clarify on it.

              • 4. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                Paul Chaney

                One more quick thing, just in case you didn't know this.  There's a dropdown on the top right of each dashboard window that has an option for dynamically fitting results to the window pane (see below).  I know this doesn't directly address your Top N issue/question, but it can be a nice option to eliminate scroll bars AND resize your new Top N results to fit the pane, if you wish to do that.  It tidies up the UI a bit.

                 

                Nice start of a dashboard, by the way!

                 

                 

                 

                Paul

                • 5. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                  Christina Gremore

                  Hi Nicholas,

                   

                  I think there's a pretty simple solution to this.

                   

                  I opened up your dummy workbook and added the Function to both PROCESS and AUDIT views. Just pop it to the leftmost spot on the row shelf (you can then right-click on the pill and de-select Show Header to hide the words.)

                   

                  Then I put Function on the filter card, and set up the Top tab, so that it filtered to the Top N based on Count of Finding Risk Rating. Then right clicked-the filter pill to Apply to all worksheets. Then do your Use-As-Filter Actions on the appropriate views in the dashboard.

                   

                  When I tried this in your sample dashboard, everything seemed to reconcile correctly, so let me know if there's an error I'm not seeing. I think the key piece that you were missing is that you're actually filtering Function across all three sheets, so that Function dimension needs to be on the vizzes that you're filtering, somewhere.

                   

                  Hope this was helpful!

                  2 of 2 people found this helpful
                  • 6. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                    Paul Chaney

                    Great solution, Christina!  Hopefully this addresses everything Nicholas needed.  I know I learned some nice stuff from your reply.  : )

                     

                     

                    Paul

                    • 7. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                      Christina Gremore

                      Figured I'd add in a twbx to show you what I mean, since you're new and my instructions may not be the clearest...

                      • 8. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                        Nicholas Smith

                        This is unfortunately one of the routes I experimented with last week that does not work for our desired view

                         

                        The trouble comes in that I also want to have a top n filter on the "Process" and "Audit" windows.  If you set the filter in these views the same way you described for the "Functions" view, the views do not function appropriately (they are not dynamic and will only ever show the top 5 processes of the entire population, no matter how the Function view is filtered in the dashboard).

                         

                        Take a look at the dashboard I updated and attached.  Notice that I have it filtered in the dashboard for blue finance findings, which total 22.  The Process Window filters to the Client Financial Management Process, and only shows 16 findings.  The processes with the other 6 findings are hidden from the view because they are not part of the top 5 overall processes, and that is how tableau applies the filter.

                         

                        The workarounds I tried last week was filtering on rank in a table function.  This would allow the process view to dynamically sort based on how the function view is sorted.  IE - it would always show the top 5 processes (or the top 1234 if there are only that many).  However, I cannot get tableau to rank based on TOTAL (it ranks each risk rating and then filters separately).  This approach works for the text tables (creating a subtotal field, table function rank, and then filtering) but for not for the stacked bar graph!  I also tried using a fixed function and the Include/Exclude functions to create a subtotal field that I could rank on.  However, the nulls in this process screw up the ranking.  I've exhausted myself trying to find workarounds

                        • 9. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                          Paul Chaney

                          This is weird, because I followed Christina's instructions and it seems to work for me.  Looks to me like it will work if you take out the Process filter from the Process worksheet.  I've attached the modified version of your first file, after I followed the steps.  I had to upgrade the version, so it may not work for you...in that case, here's a screenshot of the automatically filtered results by clicking on the 22.  Other boxes work, as well (e.g., the Finance "10" box).

                           

                           

                           

                          Paul

                          • 10. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                            Nicholas Smith

                            I don't think you have a top N filter applied to the "process" workbook, which is what we desire   What might be tripping you up is that the dummy data is extremely condensed, but the overall goal is to have a TOP N FUNCTIONS by TOTAL FINDINGS filter on the Functions tab and a TOP N PROCESSES BY TOTAL FINDINGS filter on the Processes tab (likewise, top N audits on Audits tab).  The "Process" filter you recommend removing is one that we absolutely need given the size of the actual data. 

                            • 11. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                              Paul Chaney

                              Gotcha.  Sorry, I'm in and out today and hopefully haven't done more harm than good trying to help!  : )  You've given a lot of good info on this, so if I can look back over this later I'll do so ASAP.  I'm sure you're ready to get this one solved!

                               

                               

                              Paul

                              • 12. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                                Christina Gremore

                                6-13-2016 1-37-17 PM.png

                                 

                                OK, so in your ideal world, when you click on the Blue Finance bar (22) and then the Process view filters, what would you see? Which 4 processes would show up? This is solvable, but you're not telling me what the right answer is, so I don't know what problem I'm solving

                                 

                                This is important because 4 of your processes there have the same value, so if you only want 4 to show up (instead of 6) how do we know which 2 to ditch? The default behavior would be to go by alphabetical order, but I don't think that would be a useful analysis for you.

                                 

                                Same question applies for AUDIT. When there are lots of audits sharing the same count, how do you want it to decide what to show and what to exclude?

                                • 13. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                                  Nicholas Smith

                                  I think we have a bit of lost in translation here - which is understandable   The screenshot you included your post is actually the ideal filtering if the you have drilled into the 22 Blue Finance Findings (as you have).  This is exactly what we want to show up, and we are not that concerned with the filtering of instances where there are ties (as I believe some of your questions are targeting).

                                   

                                  The issue, however, is that we also want the below view when the dashboard is entirely unfiltered.  Notice that there is no scroll bar in the function pane, as it is filtered for the top 5 mega processes by count.  We need to have top filters in all of the panes, and for those filters to function dynamically.  I didn't update this for the Audit window in the below, but it is the same idea.

                                   

                                   

                                  Conversely, this is the view in the unfiltered dashboard of the solution you uploaded earlier, where the process window shows all processes as opposed to the top N for the functions that are visible.

                                   

                                  • 14. Re: Creating a dynamic "top-n" filter on a stacked bar graph???
                                    Christina Gremore

                                    Hm, I'm not sure if there's a good way to have it set to "Only show N items unless someone makes a selection, in which case show only those items." Because as you saw, the filter that you set to only show the Top N conflicts with the filtering of selecting a different segment, and things will get dropped.

                                     

                                    What if you just didn't show anything until someone selected a Function? You can set up a cascade of filters to create a true Guided Analytics solution.

                                     

                                    6-13-2016 2-05-28 PM.png

                                     

                                    Alternately, you can select a bar to filter, and then publish it that way. Then that will be the view that people open up on, and they can change it by clicking a different segment.

                                     

                                    Would either of those solutions work?

                                    1 2 Previous Next