1 Reply Latest reply: May 15, 2014 9:56 AM by DavidS _ RSS

    Count Unique functionality in a summary

    joey

      I am creating a summary of our top accounts on a regional basis.  For each region, we want the count of accounts, as well as the count of the policies (an accounts is made up of multiple policies), and the total premium. Something like this:

       

      Region PolicyCount  AccountCount  Premium

      Central     4          2          $100

      North     6          3          $300

      South     3          1          $100

       

      The input is a spreadsheet of the policy detail that make up the top accounts.  There is a column for the account.

       

      Region     Account     Policy     Premium

      Central     1234          abcd     $25

      Central     1234          abce     $50     

      Central     5678          abcf     $10

      Central     5678          abcg     $15

      ...

       

       

      My summary has a key field of region, and two measure fields of count(*) and sum(premium).   The count field represents the number of policies that make up the top accounts in each region.

       

      However, I need a count of the number of accounts that make up each region.  I envision this as CountUnique(Account), where Count Unique counts the number of unique occurrences in the set.  There is no function in Monarch that I see to do this.

       

      I could do this with an intermediate spreadsheet that lists the number of accounts in each region.  This would be read in as a calculated field, and the final summary would list the account count as the minimum of the calculated field.

       

      I wonder if anyone has any other suggestions on a cleaner way.  Thanks for any ideas.

        • Count Unique functionality in a summary
          DavidS _

          Joey, one way to do it would be to create a summary that includes the account as a key field, and the two measures that you currently have; then, export the summary to a text file, and create a second model using the resulting export file as your input file; the second model would use the region as the only key field, and would have three measures: the sum of the amount, the record count, and the sum of the count from the first export file.

           

          It still involves more than one step, but at least you can do it all with Monarch.