1 Reply Latest reply: May 15, 2014 10:09 AM by Data Kruncher RSS

    Top 15 values

    bchao728 _

      How do I show a Top 15 in a Summary window?

      I currently have a Summary that shows Location and Vendor in the Key Fields and a sum of Sales for each Vendor in the Measures Field. I have over 30 locations and over 100 Vendors in each Location.

      I just want to see the Top 15 Vendors by Sales for each Location.

        • Top 15 values
          Data Kruncher

          As you've no doubt discovered, Monarch doesn't handle multiple key Top N summaries well at all. Single keys only please. Unfortunately, that doesn't satisfy requirements such as what you've described.


          But fear not, you can get what you want with Monarch, but it takes more initial work than you'd guess. The good news is that since we're talking about Monarch, it's still only a few minutes of effort.


          Here's my technique for such work.


          Start with your summary, but don't worry about the Top N limits. Just build your summary with Location, Vendor and SUM(Sales). Do not include subtotals or a grand total. Export that out to a file.


          New model: Import that export and add an Original_RecNo calculated field using RecNo(). Export that to a new file.


          New model: Import the first export and using an external lookup to the Original RecNo export, import the Original Recno field based on the Location field. This will give you the record number of the first occurrence of a particular location for every record for that location.


          Now add a Top 15 filter:

          (RecNo() - Original_Recno) < 15[/code]And unless you need Location subtotals, you're done. If you do need subtotals, just build a new summary that uses the Top 15 filter.


          You can make it easier to maintain and more flexible if you make the value of 15 a runtime parameter. You'll have an instant Top 3, Top 10, whatever.


          Ideally if you're doing this sort of work regularly, use a batch file and project files to make your life easier.


          I hope that I've described this properly and haven't overlooked any steps.