1 Reply Latest reply on Apr 22, 2013 6:39 PM by Joe Mako

    Top N categories by aggregate on subcategories

    Jesse Ashby

      I'm trying to show how long it takes early US Presidents to answer the phone (not really).  I would like to be able to be able to filter on the 10 presidents with longest overall average time to answer and display that president along with his average answer time during the day and also during the night.  In the attached workbook, only the presidents down to Andrew Jackson would be shown.


      The format of the data is not set in stone and can be changed easily if its a problem.

        • 1. Re: Top N categories by aggregate on subcategories
          Joe Mako

          One option, as in the attached:


          1. Create a calc field like:


          (you can replace the 10 with a parameter)

          2. place this calc field on the Marks card (Level of Detail)

          3. right-click this pill, select Edit Table Calculation

          4. in the Compute using drop-down, select Advanced

          5. move both dimensions over to the Addressing list, with the top one being President (leave Sort at Automatic)

          6. OK both dialogs, and move the calc field pill from the Marks card to the Filter shelf, keeping only when True


          There are a great deal of factors at play here, and this is not the only route to get to this solution, but I feel this route is a good balance between robustness and simplicity for your situation. If your situation is different, then there may be a better route to consider.


          For example, currently President is sorted with a manual sort, if you want a dynamic sort based on a measure, then based on exactly what you want to sort by, you may need a different route. For example, it looks like you are sorted on the sum of the averages, and you could sort the President pill on a calculated field like:

          AVG(IF [Call Type]="Day" THEN [Answer Seconds] END)+

          AVG(IF [Call Type]="Night" THEN [Answer Seconds] END)

          to produce that kind of sort.

          1 of 1 people found this helpful