6 Replies Latest reply: May 15, 2014 10:01 AM by Nancy10 _ RSS

    Subtract function in summary window

    ChristD _

      In the summary table a lot of functions are available (sum, var, pct etc) but a straight forward subtraction of two columns is not available.

       

      I have two similar reports of different dates and when these are loaded at the same time it would be helpful if the summaries for these two reports could be subtracted to see the difference between the two at various levels. Apart from the option to do this in Excel, it would be helpful if this was available in Monarch.

       

      I have been able to find that a similar question was asked in 2002 (Monarch KnowledgeBase & Archive / closed thread: "Variances in A Given Period"), but I could not find the solution to it.

       

      Christ Daamen

      V9.01 Pro

        • Subtract function in summary window
          Grant Perkins

          Christ, welcome to the forum.

           

          Options have moved on since 2002.

           

          If both fields exist at the detail record level in your source data table you should be able to create a formula in the summary measure for SUM[Field A] - SUM[Field B].

           

          On the other hand if they are separate you could export one of them and then perform a lookup using a common key as a separate lookup mechanism to ensure that both values are in the table and then do the calculation.

           

          However I suspect I may have missed some aspects of your requirements so if that does not help you please let us know.

           

          HTH.

           

           

          Grant

          • Subtract function in summary window
            ChristD _

            Grant,

             

            I have not been able to explain the issue properly.

             

            The issue is that the columns that need subtracting is in fact the same column. The print files that are loaded in Monarch are 2 similar reports which are run at different dates.

             

            In the summary view these "print dates" can be displayed in different columns by the "across" functionality. But there no option to subtract these columns in the "across" mode.

             

            Regards,

             

            Christ

              • Subtract function in summary window
                Grant Perkins

                Ok, I had missed something.

                 

                Bear in mind that the 'across' function is just a display device not a data segregation into separate fields.

                 

                I think you probably have at least two options.

                 

                The basic option, other than exporting to Excel and then having the data in separate fields in Excel,  would be to export the summary to a file in the new format and then use Monarch to convert the new file to a new table with the columns treated as separate fields just as Excel would treat them. The two step process could be set up in a batch file to make it easy to use.

                 

                Alternatively create calculated fields in your table that create any values you wish to calculate with and then you can work with the resulting fields in the summary. So if you model has a single field for, say, sales value and a separate field for the year (or month or whatever) that the value relates to,  it is relatively simple to create a calculated field for year/sales value that has a value only if the record relates to that year. Some sort of IF() function based formula should get you there.

                 

                As such field will be separate from each other in the table you can use them to calculate differences in the summary as previously suggested.

                 

                Does this help at all?

                 

                Grant