8 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Running Total

    MorseB _

      Is there a way to create a running total in Monarch?  I have a beginning balance figure that I want to add the transaction amount to create a running balance.

        • Running Total
          Grant Perkins

          Beth,

           

          I have a feeling you are looking for something a little different to this but have you tried the Cumulative Aggregations in a summary?

           

           

          Grant

          • Running Total
            MorseB _

            I do not know what or how to set up Cumulative Aggregations in a summary.

            • Running Total
              Gareth Horton

              Beth

               

              You need Monarch 7 or higher for this.

               

              In the summary, on the Calculation tab, there is a Cumulative Sum option.

               

              Gareth

               

              Originally posted by MorseB:

              I do not know what or how to set up Cumulative Aggregations in a summary. /b[/quote]

              • Running Total
                alexcn _

                How does one reset the cumulative summary for each group? I dont want to keep on adding apples and oranges but come up with a sum for each.

                • Running Total
                  Grant Perkins

                  Alex,

                   

                  Don't think you can reset and I can't think of a way to do anything useful by formula either (but then I'm not the best person to try to figure that one out.)

                   

                  You can do things that look relevant with the percentages espcecially if you display the value of the relevant field/key combination across rather than down.

                   

                  But I don't think that helps you much.

                   

                  If the result is going to be apples and oranges mixed up anyway have you considered a separate summary for each group? Or more pertinently the same summary copied but with a filter applied so that it reports only by appropriate groups? That way I guess people would be unlikely to confuse the different values. (Of course there may be good reasons why you could not go down that road.)

                   

                   

                  HTH.

                   

                   

                  Grant

                  • Running Total
                    alexcn _

                    Grant,

                     

                    Many thanks for your response.  I had been promised this functionality was included in the new version 9.0 of Monarch Pro but having upgraded / installed / lookied around I am none the wiser.

                     

                    While your suggestion would indeed work, its inpractical in operation as my source file has about 500,000 records resulting in grouped data consisting of about 300 different groups - all of which need to be summed separately.

                     

                    Any more ideas would be greatly appreciated,

                     

                    Alex

                     

                    Originally posted by Grant Perkins:

                    Alex,

                     

                    Don't think you can reset and I can't think of a way to do anything useful by formula either (but then I'm not the best person to try to figure that one out.)

                     

                    You can do things that look relevant with the percentages espcecially if you display the value of the relevant field/key combination across rather than down.

                     

                    But I don't think that helps you much.

                     

                    If the result is going to be apples and oranges mixed up anyway have you considered a separate summary for each group? Or more pertinently the same summary copied but with a filter applied so that it reports only by appropriate groups? That way I guess people would be unlikely to confuse the different values. (Of course there may be good reasons why you could not go down that road.)

                     

                     

                    HTH.

                     

                     

                    Grant /b[/quote]

                    • Running Total
                      Grant Perkins

                      Originally posted by alexcn:

                      While your suggestion would indeed work, its inpractical in operation as my source file has about 500,000 records resulting in grouped data consisting of about 300 different groups - all of which need to be summed separately.

                      /b[/quote]Alex,

                       

                      You originally mentioned a cumulative SUM reset. Do you really need the cumulative part of that? Your last description reads more like a series of group summaries and then a grand total are what you seek.

                       

                      Monarch summaries offer cumulative percentages by subgroups but not cumulative sums by sub-groups and I can't think of a way around that at the moment.

                       

                             

                       

                       

                      Grant

                       

                      [size="1"][ March 05, 2007, 11:31 AM: Message edited by: Grant Perkins ][/size]

                      • Running Total
                        Grant Perkins

                        Alex,

                         

                        It occurs to me that you have 2 (or more) measures running with one giving the running cumulative total and the others set just to SUM but with subtotals so that at least at each change of 'group' and sub-group you would get both the sub-group total and the running overall cumulative total.

                         

                        Now with an export or two there would be potential, within Monarch or possibly Excel depending on how many rows result in the summary from the intial 500k and/or whether you have or will have the Office 2007 version, to take the summarised results and measures and perform some calculations that should be able to give cumulative totals within the groups below the summary totals.

                         

                        If that is what you need.

                         

                        Just a thought that seemed to be worth mentioning.

                         

                        HTH.

                         

                         

                        Grant