2 Replies Latest reply: May 15, 2014 10:11 AM by mdyoung _ RSS

    Need help with creating summary rows

    barnold _

      Here's the problem:

      I have data in an Excel spreadsheet that looks like this:

      NNNNNNNN  2/13/2011   1034.35 AAAAAAAAAA

      NNNNNNNN  3/12/2011   1023.24 AAAAAAAAAA

      BBBBBBBBB  2/13/2011   1100.24 ZZZZZZZZZZ

      BBBBBBBBB  4/12/2011   1011.10 ZZZZZZZZZZ

       

      And I need output that looks like this:

      NNNNNNNN  2/13/2011  1034.35  3/12/2011  1023.24

      BBBBBBBBB  2/13/2011  1100.24  4/12/2011  1011.10

       

      The model needs to create the output for rows where the first and fourth columns match, and there can be a variable number of these (although the example shows just two for each record, and they already match).

       

      The basic problem is an analysis of payroll data in which we are attempting to determine the "next expected pay date" for an individual, across multiple payers, paying to multiple  payees, across a range of months.

       

      Any help would be greatly appreciated.

       

      Thanks,

      Bill

        • Need help with creating summary rows
          Olly Bond

          Hello Bill,

           

          There's almost certainly going to need to be two models to do this neatly. Assuming the fields in Excel are Payee, Date, Amount, and Payer, then a Summary in Monarch with Payee as one key, then another key of Payer which you can set to hidden (this will group the payments that match together), then Date as a key set to display Across rather than down, and Sum(Amount) as a measure. This will get you some of the way there, but you'll probably want another pass through Monarch to clean up the white space.

           

          Best wishes,

           

          Olly

            • Need help with creating summary rows
              mdyoung _

              Hi, Bill.

               

              Based on the input file information, I created a test spreadsheet with the following data:

               

              Employee      PayDate      PayAmt    Payer[/SIZE]

              ----


                 -


                 -


                 -


              [/SIZE]

              Aaaaa Aaaaa   2011-02-13   1025.22   Company ABC[/SIZE]

              Bbbbb Bbbbb   2011-02-03    957.12   Company GHI[/SIZE]

              Cccc Cccc     2011-03-01   1233.51   Company GHI[/SIZE]

              Aaaaa Aaaaa   2011-03-12   1050.31   Company DEF[/SIZE]

              Cccc Cccc     2011-03-14   1215.98   Company DEF[/SIZE]

              Cccc Cccc     2011-04-05   1301.44   Company GHI[/SIZE]

              Bbbbb Bbbbb   2011-02-18    985.60   Company ABC[/SIZE]

              Bbbbb Bbbbb   2011-03-01    850.17   Company ABC[/SIZE]

              Aaaaa Aaaaa   2011-04-15   1127.97   Company ABC[/SIZE]

              Cccc Cccc     2011-04-11   1276.99   Company DEF[/SIZE]

               

              /CODE

               

               

              After opening it in Monarch as a database, I created an external join that points back to itself and joined on the Employee[/B] and Payer[/B] columns. Monarch will detect this does not give a distinct listing and will ask if you want to use the first value it comes to for the other columns; in which, the answer is "Yes."

               

              Next, I pulled in the PayDate[/B] (renamed it JoinPayDate[/B]) and PayAmt[/B] (JoinPayAmt[/B]).

               

              Afterwards, I created a filter using the following expression:

              =[JoinPayAmt] .And. =[JoinPayDate]

              /CODE

               

              Since an employee shouldn't get paid the exact same amount on the exact same date, this, in theory, should give you the result you're looking for.

               

              I defined a sort order using Employee[/B], PayDate[/B] and Payer[/B] which gave me the following results:

              Employee      PayDate      PayAmt    Payor

              -


                 -


                 -


                 -


              Aaaaa Aaaaa   2011-02-13   1025.22   Company ABC

              Aaaaa Aaaaa   2011-03-12   1050.31   Company DEF

              Bbbbb Bbbbb   2011-02-03    957.12   Company GHI

              Bbbbb Bbbbb   2011-02-18    985.60   Company ABC

              Cccc Cccc     2011-03-01   1233.51   Company GHI

              Cccc Cccc     2011-03-14   1215.98   Company DEF

               

              /CODE

               

              I'm not sure which edition of Monarch you have (Standard or Pro), but I use Pro and I've never used Standard; therefore, if you do use standard, I'm not sure if it has the capability to perform external joins. If it doesn't, this solution isn't going to work for you.

               

              Hope this helps or at least gives you an idea on how to resolve the issue.

               

              Thanks,

               

              Micheal