2 Replies Latest reply: May 15, 2014 10:04 AM by Grant Perkins RSS

    How to group data for an export.

    JLain _

      Here is a sampling of export data that I'm working on.

       

      Problem: I would like to export the entire list of data to excel and list the data on one spreadsheet with various groupings (teams) i.e.  I would like to show AG and CM as a grouping with subtotals.

       

      I know you can build mutliple filters and multiple summaries which will export to multiple sheets in excel. What I am seeking is to group the summary data and export into one spreadsheet in an excel workbook.

       

      Any ideas?

       

      Representative      Goal       Amount of Goal      % of Goal

      AG      3,961,082.00       938,731.62      0.237

      AMH      5,337,366.00       1,065,168.98      0.2

      CM      5,115,531.00       918,360.88      0.18

      DA      16,204,903.00       1,199,626.33      0.074

      DB      726,330.00       158,539.79      0.218

      DM      6,996,176.00       1,351,655.04      0.193

      EG      7,885,806.00       1,219,735.75      0.155

      GE      6,787,540.00       1,308,099.30      0.193

      JT      26,193,176.00       3,240,480.17      0.124

      KB      -         62,828.39      

      KM      6,516,962.00       1,295,059.64      0.199

      LBD      -         47,311.42      

      LF      1,932,000.00       438,918.65      0.227

      MR      8,107,210.00       3,915,778.37      0.483

      SB      4,054,725.00       1,187,651.38      0.293

      SJ      6,772,345.00       763,062.68      0.113

      SJG      3,229,493.00       666,726.78      0.206

        • How to group data for an export.
          Data Kruncher

          In the Table window create a filter that selects both AG and CM. Name the filter, say, Group1.

           

          Maybe the Group2 filter selects AMH and DA, while Group3 selects JT and KB.

           

          Create a Group1 summary using the Group1 filter that displays Representative,      Goal       Amount and % of Goal. Be sure to include a grand total on the summary.

           

          Duplicate that summary using each of Group2 and Group3 filters.

           

          Now define a project export that will overwrite the existing file that will export the Group1 summary, and run that export.

           

          Define two additional project exports for Group2 and Group3 that will add data to the existing file, appending to the Group1 sheet.

           

          Monarch will do this provided that the field names in the summary are identical for all of the exports.

           

          Kruncher

            • How to group data for an export.
              Grant Perkins

              Are the groupings fairly consistent in that they are NOT subject to constant changes?

               

              If so have you looked at using a grouping code and then sub-totalling on that?

               

              If the incoming data does not have such a grouping code you can create one either via a calcualted field or, probably better in this case, a lookup table. An internal lookup table if the groups rarely change or, if you have Monarch Pro, perhaps an external lookup file that you (or someone else) can maintain outside Monarch if the grooups are volatile.

               

              Whixh of the three concepts will work best for you is very dependent on what the data can provide for you and how stable or otherwise the content is.

               

              HTH.

               

               

              Grant