8 Replies Latest reply: May 15, 2014 9:56 AM by Gareth Horton RSS

    Monthly Report Comparisons

    Nick Osdale-Popa

      Rooting for some ideas on how to tackle this:

       

      I have to compare a current months report to a prior months report.

       

      1) Get the delta of the grand total of both reports

      2) Show what new Costcodes/Phases/Amounts have been added since the prior month

      3) Show any new invoices/amounts that have been added since the prior month

      4) Show any changes to that have occured between the two prior items (has the amount changed?)

      5) Those changes/additions should equal the amount of the delta from item 1.

       

      I would normally just to do a data extract of each report and do all the manipulations in Excel, but this time I want to utilize as much of Monarch's power as I can.   :cool:

        • Monthly Report Comparisons
          Grant Perkins

          Hi Nick,

           

          A couple of years ago there was an interesting solution to a similar problem related to comparing 2 shipping manifests prduced a couple of weeks apart, identifying changes and reporting them in various ways. It sounds similar to your requirement.

           

          I can't recall where I read about it. I have not found it in the forum records so it may be in one of the newsletter .pdf files but I have not managed to find it in any of those I have looked at from history.

           

          Other than that I guess the totals from each report are going to be footer fields.

           

          Detail lines need to be captured with a key (real or calculated field) that allows the use of either an external lookup table as part of a 2 stage process or to capture both reports in a single table and then look at the count of lines with common keys. (and therefore ID records where there are no common keys. The UNIQUE criteria for sub-filtering may be handy as well.

           

          Just a couple of thoughts off the top of my head as I am a little short of time to give it the attention it deserves in the next few days.

           

           

          Grant

          • Monthly Report Comparisons
            Nick Osdale-Popa

            After further discussions with the user, I found that I only need to show the Invoice differences.

            The summary shows the grand total of those invoices which obviously matches the totals from the report.

             

            I have it set up in a Summary that shows the report dates as an across display, with a SUM() as the measure.  Is there a way to get the delta between the two reports per invoice total, and then only display those items that are not zero (0)?

            The summary looks like this:

            [font="courier"]Invoice 01/31/05 02/28/05 Sum(Amount)

            12345    12.35    12.35    24.70

            12346    (null)   18.85    18.85[/font][/quote]I would like it like this:

            [font="courier"]Invoice 01/31/05 02/28/05 Delta

            12346    (null)   18.85    18.85[/font][/quote](Using Monarch v7, as we haven't purchased all our copies of v8 yet)

            • Monthly Report Comparisons
              RalphB _

              Nick,

               

              Have you tried to export the data from one month out to a file and link it back in to the other month by invoice #. Then have a calculated field to calc the delta.  You can then filter out the ones that there were changes on. 

               

              Hope this helps.

               

              Ralph

              • Monthly Report Comparisons
                Grant Perkins

                Hi Nick,

                 

                I think you may need to replace the NULL with a zero as part of this.

                 

                I think (I can if I take a file in the format of you summary and create another summary ...) you could then change the SUM to be a calculated expression for date 1 - date 2 (or vice versa) and then use that expression as a selection criteria for the INVOICE number field. So if the difference of Month 2 - Month 1 is at least .01 report it otherwise lump it in with all others.

                The downside seems to be that if the value is negative (value of invoice reduced) it will be included in All Others. So I don't see an immediate way of saying 'if not zero'.

                 

                But I guess you could have 2 summaries and simply invert the formula for the second one!

                 

                Does that make sense? If not I will try to be more specific.

                 

                Grant

                • Monthly Report Comparisons
                  Steve Caiels

                  Hi Nick,

                   

                  Yep, an external lookup should do this as long as you have a unique identifying reference number (invoice number) for each transaction.

                   

                  Open up the smaller report and export it to an Access table.  Access is better than Excel as you won’t have any problems with data type.  You don’t need to have Access installed.

                   

                  Open up the larger report, then join the Access table you just created. 

                   

                  Records in the larger report that do not have a matching entry in the smaller report will have null values in the joined field. 

                   

                  As Grant said, you may need to have a calculation to take the nulls into account.  For example.

                   

                  OLD-(If(IsNull(NEW),0,NEW))

                   

                  Then a filter of OLD<>0 should identify the new or changed entries.

                   

                  One thing to remember is that this method will not detect records that are only in the smaller report.  To do this, you’d need to repeat the same process but starting with the larger report first.

                   

                  Cheers

                  Steve

                  • Monthly Report Comparisons
                    michaeljul _

                    Hi Grant,

                    The newsletter article you refer to was the 2001 fall issue....... The idea was mine.....

                    • Monthly Report Comparisons
                      Grant Perkins

                      Originally posted by michaeljul:

                      Hi Grant,

                      The newsletter article you refer to was the 2001 fall issue....... The idea was mine..... /b[/quote]Hi Michael!

                       

                      I thought it was yours but as I did not find it when I looked I could not be certain.

                       

                      I have just tried the Google search as recommended by Mike U (In fact I had just tried it immediately before I spotted your post as well) and the fall 2001 issue does not seem to exist.

                       

                      I wonder if I have a text version in my email archive ...?!

                       

                      Thanks for the advice. If you can find the reference to the article as well that would round off this thread nicely and satisfy my curiosity to re-read the solution you came up with. (Thus testing my memory.    )

                       

                      Grant

                       

                      Now, which machine will my 2001 email archive be on ....

                      • Monthly Report Comparisons
                        Gareth Horton

                        Nick,

                         

                        There have been a few approaches posted to this one, but I thought I would suggest another one, just for completeness.

                         

                        It is more an approach, than a solution to all the issues originally defined, but might be valuable.

                         

                        There are two functions in Monarch which can be used to identify the report file, File and ID.

                         

                        You could use either of these functions, depending on your need to create calculated fields which would show the amounts as per report columns, i.e.

                         

                        You create two calculated fields for the amount field, say AmountAug and AmountSept, using if statements and some clever use of the File and Rsplit, or using ID and loading the files in a particular order.

                         

                        This would have the added benefit of allowing you to get rid of nulls.

                         

                        Now you can go and create a summary, with a key field of say Customer and AmountAug and Amount Sept as measures.

                         

                        Then add AmountAug as a measure again, and edit the expression to read  SUM(AmountAug)-SUM(AmountSept) .  Note that you would have to change the order of this expression, depending on the situation.

                         

                        Call this column Net or something.

                         

                        This should then produce 0 balances for you in this measure.

                         

                        To show positive changes, you could change the matching of the key field to be Measure "Net" is at least 0.000001

                         

                        To show negative changes, change the matching of the key field to be Measure "Net" is at least -0.000001

                         

                        You could remove the AmountAug and AmountSept measures if you want to.

                         

                        The only fly in the ointment would be the existence of the All Others row, but this may have some use anyway.

                         

                        Gareth

                         

                         

                        Originally posted by Nick Osdale-Popa:

                        After further discussions with the user, I found that I only need to show the Invoice differences.

                        The summary shows the grand total of those invoices which obviously matches the totals from the report.

                         

                        I have it set up in a Summary that shows the report dates as an across display, with a SUM() as the measure.  Is there a way to get the delta between the two reports per invoice total, and then only display those items that are not zero (0)?

                        The summary looks like this:

                        [font="courier"]Invoice 01/31/05 02/28/05 Sum(Amount)

                        12345    12.35    12.35    24.70

                        12346    (null)   18.85    18.85[/font][/quote]I would like it like this:

                        [font="courier"]Invoice 01/31/05 02/28/05 Delta

                        12346    (null)   18.85    18.85[/font][/quote](Using Monarch v7, as we haven't purchased all our copies of v8 yet) /b[/quote]