5 Replies Latest reply on Jan 20, 2012 4:46 PM by kevinfooter

    Creating a Top N Parameter with a Date Filter

      I am trying to create a dashboard that allows the user to show the top N employees by # of phone calls which is relatively easy to do.  Where I am having problems is when I add another parameter that lets the user select the date parameter being the last week, month, year, etc.  The top records that are displayed are always based on the unfiltered total.  How do I tell it to filter based on the selected date parameter before calculating the top N?

        • 1. Re: Creating a Top N Parameter with a Date Filter
          Shawn Wallwork

          Kevin, post a sample packaged workbook and I'm sure some of us will take a look to see if we can figure out where things are going wrong. --Shawn

          • 2. Re: Creating a Top N Parameter with a Date Filter

            Attached is a sample version of what I am trying to accomplish.  I can get the Top N parameter to work fine and I can apply a time filter and it will work but when I try to graph the data using stacked bars to represent IN + OUT with the Top N parameter built on the total I get wierd results.

             

            For instance, if you look at the attached workbook and move the parameter from 5 to 6 Christopher appears as the 4th largest value where he previously wasn't on the list when we were showing the top 5 records.

             

            Something doesn't make sense here...

            • 3. Re: Creating a Top N Parameter with a Date Filter
              Shawn Wallwork

              Kevin, I made 1 change to your workbook and now I believe it works the way you want. All I did was rt-click the Date filter and click 'Add Context'. This added the little push-pin icon, and seems to have fixed the problem. I really don't understand what Add Context is all about (even though I've read the documentation). So maybe one of the real experts around here can give the Add Context feature some context. :) Richard? Joe? Dimitri?

               

              --Shawn

              • 4. Re: Creating a Top N Parameter with a Date Filter

                Interesting.  It appears that worked so thank you but I am confused as to why.  Ive used that feature before but only to add a filter criteria to a dashboard and have it affect other worksheets also in the dashboard.  I don't understand how adding context would change my results.

                 

                But thanks for solving my immediate problem.

                 

                Kevin

                • 5. Re: Creating a Top N Parameter with a Date Filter
                  Richard Leeke

                  I haven't looked at the workbooks, but I think I know roughly what's going on.

                   

                  Add filter(s) to Context causes those filters to be evaluated before other filters.  That is often useful as a way of avoiding apparently weird behaviour with a top N filter like this.  The way that context filters are implemented (for relational data sources) is by creating a temporary table - so the context filter populates the temporary table and then other filters (in this case the top N filter) only works on the rows in that reduced set.  Without that, the top N may apply before the other filters, which can lead to the sort of confusing behaviour you've been seeing.