4 Replies Latest reply on Apr 20, 2018 1:36 PM by Matthew Schmitt

    Script for Pulling Top 5 Categories for Each Queue

    Matthew Schmitt

      Hey everyone!  I am new to forum and just wanted first say hello and I am excited to be here.  Attached is an image of my Tableau I have been working on, which is updated weekly with stats from our ticketing software.  I am trying to write a script that will update and pull the top 5 Categories from each Queue every week when I update.  Any help will be much appreciated and look forward to hearing from you!  Thanks again.

        • 1. Re: Script for Pulling Top 5 Categories for Each Queue
          Joe Oppelt

          How familiar are you with table calcs, RANK, "restart every", table calc filters...?

           

          Those are the elements you'll want to use.  If you haven't played with them yet, upload a sample workbook and I'll help you through it.

          • 2. Re: Script for Pulling Top 5 Categories for Each Queue
            Matthew Schmitt

            Joe, thank you for the reply.  I have not had much time to work through those elements yet and would appreciate any help possible.  I have attached a copy of the workbook(I believe). 

            • 3. Re: Script for Pulling Top 5 Categories for Each Queue
              Joe Oppelt

              (V 10.4 here)

               

              In the attached I created a RANK calc.  See [Rank each queue].  I added it to the sheet on the LABELS shelf so the value shows on each bar.  But I also have to tell it to cycle through all the categories within each queue, and to restart every queue.  When you put a table calc on a sheet, tableau picks some default evaluation order.  On this sheet it initially looked like this:

               

               

              To get to that edit box, right click on the pill on the sheet and select "edit table calc".  So here tableau defaulted to table(down).  That means it's going to take all the bars down the table and rank them all.  Edit the table calc for the pill in the sample I attached and see how I changed that.  I selected "Specific Dimensions", and selected both dimensions, and told it to restart every Queue.  And you can see the results on your original Top-5 sheet.

               

              Go to copy (2).  Here I right-clicked on the [Rank Each ...] pill and changed it to DISCRETE.  That changed the pill from green to blue.  This is important.  I want to use it on ROWS, and to do what I want to do, it needs to be DISCRETE.  The viz doesn't change.  Just the pill does.  Go to Copy (3).


              Here I did ctrl-click-drag on the [Rank...] pill and shoved it between [queue] and [Category].  Tableau's sorting behavior makes a nice looking chart, and you can see RESTART is doing what you want.  You can right-click on the [Rank...] pill on the ROWS shelf and uncheck "show header" so that the 1-through-N no longer displays.  It's still on the sheet doing its job, but the user doesn't need to see the numbers here.  Go to copy (4).

               

              Here I took your request one step extra.  I made a parameter that lets the user pick a number for "N".  And then I made a calc called [Get top N].  Take a look at that.  It's a Boolean.  Each mark has  a T/F value in that calc.  I put this on filters and selected for TRUE.  (You could have just put the [Rank...] calc on filters and selected for 5 or less if your value of 5 will never change.  But usually these things don't stay static once the user sees the sheet, even if today he says, "Oh, I'll only ever need top-5..."    )

               

              See attached.

              • 4. Re: Script for Pulling Top 5 Categories for Each Queue
                Matthew Schmitt

                Joe,

                 

                Thank you so much for all of your help.  I really do appreciate it.  This turned out better than I even thought it would and thank you for going the extra mile with the help and creating that parameter for any number "N".  I wish I could like your messages 1,000 times!

                 

                Thanks again!