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

    Newbie needs some help!

    buckeyes1619 _

      I am new to using Monarch and can't quite get it to capture what I need it to capture.  Below is a segment of the report.  Highlighted is the information I need.  However, as you can see, the names and ID's not match up each line, some have PQ Sales and HomeWIRE sales while others have one but not the other.  I need a report that gives me name, ID, and # of HW sales and # of PQ sales regardless of whether they have both.  Sure hope that makes sense.

       

      "      BEST, COSANDRA (I75482)"                              

                  TAN LINES INC - 7154340249 (CPL)     PQ     Sale     6/9/2008     Billing setup     6/23/2008     C99901'

                  TAN LINES INC - 2971657214 (CPL)     PQ     Sale     6/9/2008     Billing setup     6/23/2008     C99901'

      "      Totals for BEST, COSANDRA (I75482)"                              

                  Total Sales     2                         

                  Total Requests for Reporting Period     2                         

                  Total PQ Sales:     2                         

                                     

                                     

      "      CAMERON, MARLENE (I98918)"                              

                  CHARLIE JR HENSLEY - 3977406689 (CPL)     HW     Sale     6/18/2008     Billing setup     6/18/2008     C04060'

                  EFFIE M HAYWOOD - 477241863 (CPL)     HW     Sale     6/2/2008     Cancelled after agreed     6/6/2008     C04060'

                  J E MCLEAN - 5143732468 (CPL)     HW     Sale     6/3/2008     Billing setup     6/3/2008     C04060'

                  JAMES F GREGG - 4335650166 (CPL)     HW     Sale     6/3/2008     Billing setup     6/3/2008     C04060'

                  PAMELA S ELLIOTT - 4005473592 (CPL)     HW     Sale     6/25/2008     Billing setup     6/25/2008     C04060'

                  ROSA LEE PATTERSON - 4517232676 (CPL)     HW     Sale     6/16/2008     Billing setup     6/16/2008     C04060'

                  JEN-NEL PROPERTIES INC - 5850220673 (CPL)     PQ     Sale     6/4/2008     Assigned to contractor     6/6/2008     C99902'

                  J E MCLEAN - 5143732468 (CPL)     PQ     Sale     6/5/2008     Assigned to contractor     6/9/2008     C99902'

                  JAMES M BROWN - 3466266750 (CPL)     PQ     Sale     6/9/2008     Assigned to contractor     6/11/2008     C99901'

      "      Totals for CAMERON, MARLENE (I98918)"                              

                  Total Sales     9                         

                  Total Requests for Reporting Period     2                         

                  Total HomeWIRE Sales:     6                         

                  Total PQ Sales:     3                         

                                     

                                     

      "      CHEVERIE, MARY (I44575B)"                              

                  GROVER  BARNES - 6314057404 (CPL)     HW     Sale     6/20/2008     Cancelled after agreed     6/23/2008     C04060'

      "      Totals for CHEVERIE, MARY (I44575B)"                              

                  Total Sales     1                         

                  Total HomeWIRE Sales:     1                         

                                     

                                     

      "      CHEVERIE, MARY (I44575)"                              

                  HOBERT MONROE MABE - 8434564087 (CPL)     HW     Sale     6/12/2008     Billing setup     6/12/2008     C04060'

                  HOBERT MONROE MABE - 8434564087 (CPL)     PQ     Sale     6/19/2008     Billing setup     6/19/2008     C04061'

      "      Totals for CHEVERIE, MARY (I44575)"                              

                  Total Sales     2                         

                  Total Requests for Reporting Period     1                         

                  Total HomeWIRE Sales:     1                         

                  Total PQ Sales:     1                         

                                     

                                     

      "      DIES, THOMAS (I44110)"                              

                  ELLEN JOYNER HAYNES - 46874764 (CPL)     PQ     Sale     6/15/2008     Assigned to contractor     6/16/2008     C99902'

      "      Totals for DIES, THOMAS (I44110)"                              

                  Total Sales     1                         

                  Total Requests for Reporting Period     1                         

                  Total PQ Sales:     1

        • Newbie needs some help!
          Data Kruncher

          Hello, and welcome to the forum.

           

          There are probably a couple of ways to tackle this one. Here's one...

           

          Make one of the CPL lines a single line detail template. I used the ) character as a floating trap, then captured the 7 characters like HW Sales or PQ Sales as a "Sale Type" field. I suppose 2 characters would've been enough.

           

          Next, I added a single line append template trapping the double quote in the first column. The lines for the totals will show as selected, but they won't be included in the table. Paint a single field wide enough to capture everything. I called this "Name and ID".

           

          Now in the table, create a few calculated fields. First up, split out the Name, with this formula:

           

          [SIZE=2]trim(substr(left(,instr("(",trim(substr(,3,len()-2)))),2,40))[/SIZE][/code]

          It's a bit messy looking, but it works.

           

          Next, extract the ID Number:

          [SIZE=2]substr(,instr("(",[Name and ID])+1,6)[/SIZE][/code]

           

          To count each type of sale, we need "PQ Sales" as a numeric field:

          [SIZE=2]if(="PQ Sale",1,0)[/SIZE][/code]

           

          and "HW Sales", also numeric:

          [SIZE=2]if(="HW Sale",1,0)[/SIZE][/code]

           

          Finally, build a summary with the Name and ID fields as keys, and PQ Sales and HW Sales as the measure fields.

           

          Alternatively, you could not create the PQ Sales and HW Sales fields, and create the summary using the Sales Type field as an "across" key in your summary, with the count(*) as the measure key. The advantage to using separate calculated fields is that you don't have a total for the across key if you don't want or need it.

           

          HTH,

          Kruncher