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

    Indentifying the latest date

    Lynette _

      Hi,

      I have reports generated over 2 different dates and all is well ..until now when i need to find the differences.

       

      I have summaries the calculables using the report dates accross... but now is the challange... is it possible (am sure it is) to subtract the amounts in the column under the most recent date from the amounts under the column in the older date.

       

      p.s. it may be that some lines appear / or are no longer existant in the latest report as they have been adjusted/added.

       

      Please assist..Lynette

        • Indentifying the latest date
          Grant Perkins

          Hi Lynette,

          If I understand correctly you have a value column that you are displaying ACROSS based on 2 date related columns and want to deduct the value in one column form the value in the other. Correct?

           

          You only have the SUM function to play with for the summary values. One way to get the numbers would be to create a calculated field from the Amounts you have and make one of the values negative.

           

          So

           

          IF(Date>{Earliest Date}, (0-Amount), Amount)

           

          This will give a value of Amount for the earliest date and -Amount for the later date.

           

          Now use the New field as the summary field in your Summary and make it a SUM calculation. You will se the difference between the figures, which is what you are looking for I think.

           

          Not nice viewing but works.

           

          If you had V6 or 7 you could prompt for the comparison date when the process is run ... with 5 I think you need to amend the model each time BUT someone may have a way around that (or you could use an external lookup as previously mentioned in the forum ....)

           

          Hope I got it right and this helps but welcome to see a more refined offering if anyone has one. Let me know when you find the problems!

           

          Grant

           

           

          Originally posted by Lynette:

          Hi,

          I have reports generated over 2 different dates and all is well ..until now when i need to find the differences.

           

          I have summaries the calculables using the report dates accross... but now is the challange... is it possible (am sure it is) to subtract the amounts in the column under the most recent date from the amounts under the column in the older date.

           

          p.s. it may be that some lines appear / or are no longer existant in the latest report as they have been adjusted/added.

           

          Please assist..Lynette /b[/quote]

          • Indentifying the latest date
            Lynette _

            Thanks Grant,

             

            I guess i will use the external option...Looks like I better speed up and purchase the 7Pro...

            Thanks again. Lynette

            • Indentifying the latest date
              Grant Perkins

              Originally posted by Lynette:

              Thanks Grant,

               

              I guess i will use the external option...Looks like I better speed up and purchase the 7Pro...

              Thanks again. Lynette /b[/quote]I would certainly recommend it!

               

              For completeness I guess I should have also suggested exporting the summary you had already achieved as either a file (csv or tab or fixed length or just do an Excel export)or a print to disk 'report' and then reading it back in to Monarch if required as either a report or a database (Pro versions) in order to perform the calculation between the two fields. By the time you have your summary the difficult bit has already been done.     

               

              Grant

              • Indentifying the latest date
                Lynette _

                Hi...

                Was a wee bit smart and had done that already... but am struck with "zero" in the difference

                columns...

                 

                Is there a way to filter out the zero amounts once

                I am in "Summary"??

                 

                Lynette...

                • Indentifying the latest date
                  Grant Perkins

                  Originally posted by Lynette:

                  Hi...

                  Was a wee bit smart and had done that already... but am struck with "zero" in the difference

                  columns... /b[/quote]Thought you might have done ...

                   

                  Just to confirm - is the zero value the result of the same amount appearing in both columns (each report) of the original record? And your output requirement is to provide a list for ONLY records that have changed their net value?

                   

                  [b]

                   

                  Is there a way to filter out the zero amounts once

                  I am in "Summary"??

                   

                  Lynette... /b[/quote]Can't think of a way. But of course if you export your original summary and use that as a report (if you need or choose to re-examine it with Monarch) you can calculate the difference as for a 'normal' report and filter out anything which shows zero as the result and so avoid the need for another summary completely.

                   

                  On the other hand someone out there may have a neat trick to deploy ...

                   

                  Any offers?

                   

                  Grant