4 Replies Latest reply: May 15, 2014 9:52 AM by D Batey RSS

    calculations on summary fields

    D Batey

      I am reading some financial information into Monarch that I want to output into Excel.  Two of the fields captured are total amount and total quantity.

       

      I have created a summary that sums up these two figures by the item description, each item may appear on more than one detail line.  What I need to calculate is the unit price for each item.  How do I do this?

       

      I have tried created a field for unit price but when I summarise this I can only sum or average it.  Neither of these give the right answer.  In effect I need a weighted average of the unit prices of these lines (OR total amount divided by total volume).

       

      Any ideas how I can get a unit price for an item when the item will appear on more than one detail line with very different volumes in each line. 

       

      Thanks

       

      Daniel

        • calculations on summary fields
          Grant Perkins

          Hi Daniel,

           

          Thinking in terms of a weighted average value the only thing I can imagine doing is totalling the quantity and the values and then dividing the total value by the total quantity.

           

           

          If you are producing a summary with mutliple lines for the same item, is there any way you can see to regroup the summary so that at some point all occurences of the same item description will meet up on the same line? My inital reaction would be to make the Item description a key field in the summary (or a version of it), assuming that the descriptions are indeed identical.

           

          Failing that wouold it be possible to generate a pseudo code that links the spearated records together and then use that as a key field in the summary?

           

          Or have I missed something?

           

           

          Grant

           

           

          Originally posted by D Batey:

          I am reading some financial information into Monarch that I want to output into Excel.  Two of the fields captured are total amount and total quantity.

           

          I have created a summary that sums up these two figures by the item description, each item may appear on more than one detail line.  What I need to calculate is the unit price for each item.  How do I do this?

           

          I have tried created a field for unit price but when I summarise this I can only sum or average it.  Neither of these give the right answer.  In effect I need a weighted average of the unit prices of these lines (OR total amount divided by total volume).

           

          Any ideas how I can get a unit price for an item when the item will appear on more than one detail line with very different volumes in each line. 

           

          Thanks

           

          Daniel /b[/quote]

          • calculations on summary fields
            D Batey

            Hi Grant

             

            Thanks for the response.

             

            I can group the item fields together in the summary panel so that I see the total quantity and amount for that description.  But what I can't seem to do is then divide one total by the other in the summary panel.  Is this possible?

             

            Thanks

             

            Daniel

            • calculations on summary fields
              Gareth Horton

              Daniel

               

              In V7 you need to do a measure calculation something like this:

               

              Sum(Amount)/SUM(Qty)

               

              So you would have to have your amount and qty fields defined as measures.  You would not the unit price calculated field involved at all.

               

              Just add amount as a measure again, after you have added it for the sum calculation type, double click on it, then on the calculation tab select other and edit the expression to what you desire.

               

              In V6 you could use a Ratio aggregation to do the same thing.

               

               

              Gareth

               

              Originally posted by D Batey:

              Hi Grant

               

              Thanks for the response.

               

              I can group the item fields together in the summary panel so that I see the total quantity and amount for that description.  But what I can't seem to do is then divide one total by the other in the summary panel.  Is this possible?

               

              Thanks

               

              Daniel /b[/quote]

              • calculations on summary fields
                D Batey

                Thanks, that works fine.  This is a very useful feature, if a little confusing to configure, perhaps it should be included in the training manual somewhere.

                 

                Thanks for your help.

                 

                Daniel