1 Reply Latest reply: May 15, 2014 10:12 AM by Olly Bond RSS

    Creating multiple sum iterations

    joe.lovati _

      I'm trying to profile a large transaction table in an attempt to find a match to a total value.  My goal is at acct level create sum iterations, sum of 1, sum of 2, sum of 3, ect to try and find where the sum of one of the iterations matches the total.   


      Total Example:

      Acct      Total

      A0     100.00

      A1     150.00

      A2     275.50

      A3     200.00


      Detail Example:

      Acct       Amount

      A0     49.25

      A0     50.75

      A0     100.00

      A0     200.00

      A0     300.00

      A1     34.99

      A1     75.50

      A1     39.51

      A1     44.99

      A2     58.88

      A2     78.88

      A2     48.88

      A2     88.88

      A3     39.99

      A3     40.01

      A3     39.98

      A3     40.02

      A3     40.00


      What I'm trying to get to is this, so I can do a if statement that tells me where theres a match, in this example A0 has a match at a sum of the 1st 2 records and A1 has a match when you add the 1st 3 records:

      Acct      Total     Sum of 1st Detail  Sum of 1st,2nd Detail  Sum of 1st,2nd,3rd Detail, ect.

      A0         100.00       49.25                        100.00                   200.00

      A1         150.00      34.99                         110.49                   150.00

        • Creating multiple sum iterations
          Olly Bond

          Hello Joe,


          I think there's a way to do this from a summary of the detail data, which ix exported as a table and from which an external lookup is made to the total data, so it's at least a two project job. I'm not in front of Monarch now to prototype it, but I'll take a look on Monday. I've got a feeling you might need an initial prep stage on the detail data - just a couple of questions, though... What's the upper limit on the number of transactions for an Ax record? If there are never more than 40, there might be an MCR trick we can use. If there can be more than 254, then a trick with Page() & Line() will need careful handling.


          Best wishes,