3 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Difference From Previous Report

    Paul P

      I currently have a process where I extract information from a couple of different reports that we receive each month, then export the data to Excel, and run a pivot table using the "Difference From" "Previous" to calculate the difference between reports.  Is there a way to use the summary feature in Monarch to calculate the difference between fields where the "key" is a report name.  I would like the summary to calculate the difference between the extracted data from this months report and last months report.

        • Difference From Previous Report
          Nigel Winton


          You could try using the External Link to the previous report, to bring in the previous result,changing the name of the imported column so it does not conflict with the any column and then calculating the difference.

          The 'Old Report' will always need to have the same name to keep the link, but this should overcome your problem.  I would try making the 'Old Report' from the table so you definately are linking similar fields, the doing your summary on the result and exporting that as your final report.

          You will need to experiment with when you export the 'New Report' to be ready for next months work, as you may not be able to overwrite it when you are still linking.





          • Difference From Previous Report
            Grant Perkins

            Further to Nigel's response, you really need the Pro version for the lookups but the potential to do the analysis is certainly there.


            2 or 3 years ago the process was described in one of the Monarch Newsletters which should be on line but I must confess I could not find it when I went searching. I think the particular issue is (or was) missing from the archive.


            The process reported, which is similar to some database file aggregations I have done as one-off rather than recurring activities, takes into account that the reports may vary by containing records which do not exist in both reports as well as records which do exist but have changed values.


            If I can find a link to the original description I will post it.




            • Difference From Previous Report
              Grant Perkins

              Found it!




              Page 5.


              This gives the basic theory and some suggestions of how it can be extended into calculated differences though that might get quite complex dependent on field content.


              The solution was developed in a fairly old version of Monarch so I suspect that there are a few new features in the summaries which could help your quest.


              That gives me an idea for some experimentation ...