1 Reply Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Filtering Names

    Snowflame _

      Using V9.  I am trying to create a summary of our top 10 accounts by revenue.  I need to sort on NAME of account because the same account may have several different account numbers.  However the names of the same account may be slightly different eg;  "KOITO" could also read "KOITO FRS".  How can I create a summary that will take into account this anomaly.

        • Filtering Names
          Grant Perkins

          Originally posted by Snowflame:

          Using V9.  I am trying to create a summary of our top 10 accounts by revenue.  I need to sort on NAME of account because the same account may have several different account numbers.  However the names of the same account may be slightly different eg;  "KOITO" could also read "KOITO FRS".  How can I create a summary that will take into account this anomaly. /b[/quote]It sounds like you need to group and subtotal and then sort on the subtotal to get the results you need. Here are some thoughts.

           

          1. Does your database have a code that links the accounts to a master account? (Many do these days) If so I would recommend using that as the most likely reliable method - providing the system is not being abused and the link code used for other purposes.

           

          2. If the code does not exist but you can define the rules for it you could assign a grouping code for each account and then use that for the final grouping and analysis.

           

           

          So if you can be sure that any name containing the character string "KOITO" belongs to the group you could create a calculated field and use the INSTR() function on the name field to identify all Names with KOITO in them and then allocate a code in the new table field. An IF() function would also be required to provide the instruction about what to do if KOITO does not appear.

           

          You may need a number of IF() statements, nested or listed, to cover your entire Account list, though I suspect you may be able to reduce the requirement sonce many accounts will clearly have little activity and could be ignored.

           

          3. If your system does not have the grouping code you could create one yourself and use it as a lookup - either internal or external (if you have Monarch V9 Pro). Doing that would allow you to create a master list of groupings, perhaps maintained externally, and re-use it whenever you need it. It would not matter if it was based on Name or account number and may be, ultimatley, more reliable than trying to use the Name. Name may not be unique in all cases. You may also have groups to be analysed that cannot be grouped by name.

           

           

          Once you have achieved your grouping and run the analysis the final sort and selection of the top 10 would be something for a Summary to address as you have already identified. The keys for the summary would be something like Group Code, Account (or Name), any further breakdown you might find useful and then the values as measures, with the primary field being sorted by the appropriate measure values and the 'Matching' tab set to the First "10" values. That may return more than 10 groups as the matching will be on the measure BUT realistically in most cases to have different groups with identical total values would be rare. And if it was to occur then limiting to 10 Groups only could well be the wrong thing to do!

           

          HTH.

           

           

          Grant