1 2 Previous Next 19 Replies Latest reply on Jun 4, 2012 6:34 AM by Jonathan Drummey

    Top N by Metric by most recent month??

    Stan Bush

      Hi all, I have the following challenge:

       

      I have a worksheet where I need to show which doctors had the highest case volume for the most current month.

      I've attached a packaged workbook with this data.

       

      Here's some sample data:

       

      I want the top 3 Doctors by cases, by the most recent month-year, so I need a filter to return the highlighted records:

       

      Region.png

       

      A top 3 filter doesn't work, I assumed it wouldn't - but wanted to try anyway.

       

      filter.png

       

      This seems to take the top 3 Docs based on the highest cases they've had by month:

       

      Region.png

       

      Table with resulting filter which is not what I want:

       

      tablewfilter.png

        • 1. Re: Top N by Metric by most recent month??
          George Lee

          Hi Stan,

           

          See my solution attached. Please note that I'm hard coding the latest month as #March 01, 2011# though (see the calculated field, March Case). I recently had to do something similar. In my dashboard, I connect to a database and determine the max month in the SQL code. Hope this helps.

           

          George

          • 2. Re: Top N by Metric by most recent month??
            Darin Coulter

            Hi Stan, See if the attached does what you were hoping.  It basically gives the top 3 Doctors for each month with the month's descending.

             

            If it is what you wanted, I used this link to get to where you wanted to be:

             

            http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category

             

            I duplicated the date dimension and basically just followed the directions from there.

             

            Hope this helps.

             

            Darin

            • 3. Re: Top N by Metric by most recent month??
              Stan Bush

              Thanks Darin,

               

              Is there a way to get this to work in a table format? Mine looks like this:

               

              Region.png

              • 4. Re: Top N by Metric by most recent month??
                Darin Coulter

                This was the best I could do to get it as close to your requested format.

                 

                Both version are in the .zip folder.

                • 5. Re: Top N by Metric by most recent month??
                  Jonathan Drummey

                  Here's an alternate version that doesn't have the duplicated rows. The solution uses a nested table calculation to identify the top 3 physicians for the last month:

                   

                  LOOKUP([Index],LAST())

                   

                  Where [Index] is the INDEX() function, which in the nested table calc has its Compute Using set to Advanced... with Month, Year of Date and Doc used for addressing, sorted on Cases/Sum/Descending, restarting every Month, Year of Date. This returns a 1 to N for each month, with the doc with the highest # of cases first. The LOOKUP() function has its Compute Using set to Date, so what it does is performs the lookup to get the value for [Index] for each Date for each Doc. The LAST() function in the lookup is an offset to get the last row, so the function returns the value of [Index] for the latest month for every month.

                   

                  With this function on the Filter shelf, the list can now be filtered for specific docs who are in the top N.

                   

                  You can sort the list with a similar table calc if you wish (since Tableau can't sort dimensions by the results of table calcs), I did that in the attached.

                   

                  Jonathan

                  • 6. Re: Top N by Metric by most recent month??
                    Stan Bush

                    Hi Jonathan,

                     

                    Thanks for your reply.

                     

                    I'm having trouble implementing your solution in my production version.

                     

                    Can you please walk me through any special steps? Is there any more to it than creating the calculated fields and implementing the filter?

                     

                    How do you create the Top 3 Docs Filter? When I do it, I only get 1 value in the Quick Filter.

                     

                    Here's the steps I took:

                     

                    1. Drag Top 3 Docs Filter Measure to Filters shelf.

                    2. Edit Table Calculation for Top 3 Docs Filter on Filters shelf - change to Compute Using Date, at the level Deepest, Restarting every: None

                     

                    When I turn on the Quick Filter, I get a range of 3 to 3????

                     

                    What am I doing wrong?

                     

                    Thanks for your help.

                    • 7. Re: Top N by Metric by most recent month??
                      Jonathan Drummey

                      Hi Stan,

                       

                      There are a few steps beyond the step 2 you'd listed. The Top 3 Docs Filter is a nested table calculation, which lets us set the compute using for each component calc (the Top 3 Docs Filter calc and the Index calc). This needs to be done so we have a sorted list of docs that the filter calc can correctly select from.

                       

                      Here's the whole thing:

                       

                      1. Drag Top 3 Docs Filter to the Filters shelf.

                      2. Go to Edit Table Calculations, change Compute Using to Date, at the level Deepest, Restarting every None:

                       

                      screenshot1.jpg

                       

                      3. Next, click on the Calculated Field combo box and choose Index:

                       

                      screenshot2.jpg

                       

                      4. The Table Calculation display changes to be the settings for the Index measure. Click on the Compute Using combo box and choose Advanced... The Advanced dialog opens:

                       

                      screenshot4.jpg

                      4. Set the Compute Using to Month, Year of Date and Doc (in that order), with Order Along Cases/Sum/Descending. Then click OK to return to the Table calculation window.

                       

                      5. Now you'll set the At the level: Deepest and Restarting every to Month, Year of Date, as in this screen:

                       

                      screenshot3.jpg

                       

                      6. Click OK, and you should be all set!

                       

                      Jonathan

                      • 8. Re: Top N by Metric by most recent month??
                        Stan Bush

                        Hi Jonathan,

                         

                        Many thanks! I have the table working now!

                         

                        One more thing, I'm sorry. I need the same filtering on a different viz - a line graph - is it possible?.

                         

                        The goal is a dashboard that looks like this:

                         

                        The top N table, with the same population visualized as a line graph in a dashboard:

                         

                        linetopn.png

                         

                        Attached is the workbook you've helped me with with the line worksheet included.

                        • 9. Re: Top N by Metric by most recent month??
                          Jonathan Drummey

                          Hi Stan,

                           

                          The PNG isn't showing up in any browser I'm using (IE, Safari, Firefox, Chrome) so I'm not sure what your goal is. Can you either put the PNG into a dashboard in the file or email it to me at qm.jtd (at) smmc.org?

                           

                          Thanks,

                           

                          Jonathan

                          • 10. Re: Top N by Metric by most recent month??
                            Stan Bush

                            Hi Jonathan,

                             

                            What I'm trying to achieve is on the "Dashboard 1" sheet in the file attached to my prior post.

                             

                            Here's the png attached....

                            • 11. Re: Top N by Metric by most recent month??
                              Stan Bush

                              Hi Jonathan,

                               

                              I emailed you also. Sorry for the spam....

                               

                              The view in “Dashboard 1” (attached) is what I’m trying to achieve. A table with a line graph above.

                               

                              Is it possible to apply a table calculation to a line graph?

                              • 12. Re: Top N by Metric by most recent month??
                                Jonathan Drummey

                                Hi,

                                 

                                Sorry for missing your post! It is totally possible to use a table calc to help generate a line graph. There were two issues with the workbook:

                                 

                                1. The line graph had a different set of date pills, instead of the discrete (blue) MY(date) there was a discrete YEAR(Date) and continuous MONTH(Date). This caused the Index calculation that is a sub-calc of the Top docs filter calc to not return correct results.

                                 

                                2. Table calculation filters cannot be used as global filters across worksheets on a dashboard, so the Top docs filter calc would only filter the worksheet it came from.

                                 

                                To get around issue #1, the easiest solution was to duplicate the table worksheet that has working calcs and rearrange the pills to create a line chart. That way we don't have to mess with the Compute Using settings of the table calculation.

                                 

                                To get around issue #2, I created a "# of Docs to Display" parameter, then a new "Top Docs filter (updated)" that returns True if the Doc # is <= the chosen # of Docs to Display. I replaced the existing Top Docs filter on both worksheets with the updated filter and set the Compute Using, and put the parameter on the dashboard.

                                 

                                Hope this helps!

                                 

                                Jonathan

                                • 13. Re: Top N by Metric by most recent month??
                                  Stan Bush

                                  Hi Jonathan,

                                   

                                  Sorry to bother you again....

                                   

                                  Thanks very much for the reply, but the graph doesn't update. When I goto the "line graph(updated)" sheet I get this error:

                                   

                                  "The table calculation for Top docs filter (updated) requires a field that is missing. Please add the missing field to the view."

                                   

                                  tableau_error.png

                                  • 14. Re: Top N by Metric by most recent month??
                                    Tracy Rodgers

                                    Hi Stan,

                                     

                                    My guess is the Top docs filter (updated) needs to change how it's being computed. Right click on it and select Compute Using-->make sure it is set to Date.

                                     

                                    -Tracy

                                    1 2 Previous Next