2 Replies Latest reply: May 15, 2014 10:08 AM by Bill Watson RSS

    Adding subtotals from different summaries

    Amadeus23 _

      Is there a way to add/subtract subtotals in a summary from other summary tabs?  Some of these summary tabs use the same information, just filtered differently (ie current month, prior month..etc)

       

      Basically, our report has anywhere from 3-8 summary tabs, each one filtered differently, but we'd like to automate some of the manual double checking by adding some of these together.

       

      Is this possible?

        • Adding subtotals from different summaries
          Data Kruncher

          You'd need to export your summaries and then combine those exports to get an overall total. Further, to get Monarch to easily combine the various exports, they'd need have identical the same column structures.

           

          As I envision it, you'd start with a master, all-values-in summary with positive values. You'd probably want to start preparing this with a copy of your regular model, by the way, so as to not modify your original solution.

           

          Ideally, each summary would only have a single record/line. Maybe use your company name (or other title with the same value for every[/I] record) for the single key field, and turn off the grand totals to get this look.

           

          Then add summaries with negated values.

           

          Build a project export which exports the first summary with positive values to an Excel file, and then appends the subsequent summaries to the same worksheet in that Excel file. This is why each summary must have the same structure (hat tip to Nigel Winton).

           

          Now build another Monarch model which opens this Excel file as a data source and build a summary which calculates a grand total.

           

          Finally tie it all together with a batch file which performs the various steps needed automatically.

           

          This can be done. You might need to add some dummy zero value calculated fields in the Table so that you can add them to the summaries which don't have the same structure as the "lead" summary, without affecting the totals. Just be sure to change the measure names in each summary to be consistent. It doesn't matter what the actual field names are; just the names as they appear in the summaries.

           

          HTH,

          Kruncher

            • Adding subtotals from different summaries
              Bill Watson

              another method would be to use transfer the formulas contained in the filters to a single overall "FILTER" field, with each filter formula creating a different value:

               

              e.g

              Current Month Filter is month(report_date) = month(Today())

              Prior Month Filter is month(report) < month(Today())

               

              the FILTER field formula would be

               

              if(month(repdate)=month(Today()),'CURRENT',if(month(repdate)<month(Today()),'PRIOR',''))

               

              you would then change your individual filters to:

              Current Month Filter is FILTER='CURRENT'

              Prior Month Filter is FILTER='PRIOR'

               

              Ok that is the set up out of the way, now we can also use the FILTER field in individual amount fields in the table view. These could then be hidden and used in a summary view.

               

              e.g

              CURRENT_AMT is if(FILTER='CURRENT', AMOUNT, 0)

              PRIOR_AMT is if(FILTER='PRIOR', AMOUNT,0)

               

              Then in your summary, set your key fields as required

               

              pull in AMOUNT, CURRENT_AMT and PRIOR_AMT as measures. Thean pull CURRENT_AMT in as a measure again, but edit the formula to be SUM(CURRENT_AMT) - SUM(PRIOR_AMT) if you want to do month on month comparisons, or sum(amount) - (sum(current_amt) + sum(prior_amt)) to check for differences from the original amount column.

               

              This would allow you to do single pass runs through the report with some fairly comprehensive summaries depending on a single filter field 

               

              have fun.