7 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Sum over cells concerning the same number

    Oli _

      I've following prob

       

      I want to sum 1001

       

      1001  5

      1001  9

      1001  10

       

      1002

      1002

      1002

       

      What field or function should I use ?

        • Sum over cells concerning the same number
          Grant Perkins

          Oli,

           

          If I understand your need correctly you will have a field which hold the values for the code "1001" and in 3 records the fields will have the values 5, 9 and 10.  You want to sum these to a single field with a total value of 24.

           

          I would use a summary in which the field that contains the values 5, 9 and 10 is identified as a "Measure" and is a SUM type.

           

          How you identify that the field belongs to the code 1001 rather than 1002 or some other value is a different question and may have several other solutions. Do you have any difficulty to identify the code for the value?

           

           

          I hope this helps. Let us know if this problem is more complex than I have understood.

           

          Grant

           

           

          Originally posted by Oli:

          I've following prob

           

          I want to sum 1001

           

          1001  5

          1001  9

          1001  10

           

          1002

          1002

          1002

           

          What field or function should I use ? /b[/quote]

          • Sum over cells concerning the same number
            Oli _

            Hi Grant,

            thank you for your message.

            You see the problem and I've generated a summary field. But now I have the problem that I want to get this field in the original table view. So the result of the summary is implemented. I use in the original table view a filter which excludes the double values. For example I have only the "1001" concerning datas without doubles and with the summary field. Could you please give me an advice how I can integrate a calculated summary field in the original table. Or is it possible to get the colums of the original table in the summary area ?

             

            best wishes,

            Oli

            • Sum over cells concerning the same number
              Grant Perkins

              Hi Oli,

               

              You want something that looks like this?

              [font="courier"]1001     5

              1001     9

              1001    10

              24 . . 1002 1002 1002 xx

              .

              .

               

               

               

              Or

              .

              .

               

               

              1001     5

                       9

                      10

              24 . . 1002 xx[/font][/quote]In that case you will need the summary screen and it should be possible to include the columns from the report in full detail and just add subtotal lines for the KEY field that is 1001 (and presumably 1002 and so on.)

               

              When using summaries all 'Measure' fields appear to the right of the Key data fields. In your 2 column example you can recreate the original format and add the subtotal and still keep the original format of the report or table (except for the subtotal lines.

               

              More complicated reports may not allow that to be achieved directly in a summary. But it would still be possible to report all of the table fields and the values and have sub-totals which applied at whatever drill-down level was displayed. The columnar positioning might be a little different.

               

              If you 'right click' on the line for the field in the 'Key Field' window you should find the options you require for sub-totaling

              Right click on the 'measures' window to make changes to the attributes of the calculated values (if required).

               

              Does this help?

               

              Grant

               

               

                 Originally posted by Oli:

              Hi Grant,

              thank you for your message.

              You see the problem and I've generated a summary field. But now I have the problem that I want to get this field in the original table view. So the result of the summary is implemented. I use in the original table view a filter which excludes the double values. For example I have only the "1001" concerning datas without doubles and with the summary field. Could you please give me an advice how I can integrate a calculated summary field in the original table. Or is it possible to get the colums of the original table in the summary area ?

               

              best wishes,

              Oli /b[/quote]

               

              [size="1"][ August 06, 2004, 09:35 AM: Message edited by: Grant Perkins ][/size]

              • Sum over cells concerning the same number
                Oli _

                Hi Grant,

                I already use a summary field (after different tries). I explain my problem in this exampple.

                 

                 

                I have different customer numbers. (1001 , 1002, 1003 ....) Every customer number has different orders. For the order volumes I have to create a total sum for every customer.

                c-Nr  Order-Value

                1001  5

                      6

                      10

                total 21

                 

                Further more every customer has one open Item and all the specific datas which I want to include.

                 

                When I go to my table view. Because every customer has more than one orders I see the customer numbers so often like orders a customer has

                Customer  order value  Open item    ....   ....

                1001      5             33

                1001      9             33

                1001      14            33

                 

                So use a filter that eliminates the doubles.

                That functions well. But gives me the wrong value for the order value. In this example the order value for customer 1001 would be 5.

                 

                When I create a summary field that functions well, too. But the summary gives me only the correct sum value for every customer when I switch off the filter.

                 

                Either there is a way to include the colums in the summary view or the summary field is shown in my table view (prefered).

                 

                Best wishes,

                Oli

                 

                [size="1"][ August 07, 2004, 07:15 AM: Message edited by: Oli ][/size]

                • Sum over cells concerning the same number
                  Grant Perkins

                  Oli,

                   

                  Filtering to elimitiate duplicates is not the best way to go here.

                   

                  If you create your summary with the Key fields

                   

                  Customer Number

                  Order Value

                   

                  and the MEASURE

                  Order Value  (as a SUM (See: Property >> calculation))

                   

                   

                  You would see a line for every record in the table - like your sample.

                   

                  If you then, in the summary window, drill UP this will reduce to a single line for each customer and SUM the order values at the same time. That should be what you are looking for I think.

                   

                  By the way, if you include a COUNT measure as well you should see a field that tells you how many records exist for each key combination, if that is of interest.

                   

                  Now for the OPEN ITEM.

                   

                  Is this a figure from the report that is ALREADY a summary figure for all open items for the customer? Or is it another figure produced by a calculation from the detail on the report?

                   

                  I guess it is a fixed number that you are reading from an append related to the customer number rather than from each detail line. (However perhaps the report is of a format that repeats sumary data on each line for some reason. That sort of thing is not unknown!)

                   

                  Ii seems like the field needs to be reported as it is - it would be wrong to provide a SUM calculation. It will always me constant.

                   

                  Is that correct?

                   

                  If so make the field a MEASURE and set the properties for Calculation to MAX (or MIN or AVERAGE since they should all give the same result in this situation). Using version 7 the UNIQUE measure property should also work.

                   

                  The OPEN ITEM summary calculation will then provide the same number for all drill levels for a customer number.

                   

                  Do you require a total for the Open Item values also?

                   

                  Grant

                   

                   

                  Originally posted by Oli:

                  Hi Grant,

                  I already use a summary field (after different tries). I explain my problem in this exampple.

                   

                   

                  I have different customer numbers. (1001 , 1002, 1003 ....) Every customer number has different orders. For the order volumes I have to create a total sum for every customer.

                  c-Nr  Order-Value

                  1001  5

                        6

                        10

                  total 21

                   

                  Further more every customer has one open Item and all the specific datas which I want to include.

                   

                  When I go to my table view. Because every customer has more than one orders I see the customer numbers so often like orders a customer has

                  Customer  order value  Open item    ....   ....

                  1001      5             33

                  1001      9             33

                  1001      14            33

                   

                  So use a filter that eliminates the doubles.

                  That functions well. But gives me the wrong value for the order value. In this example the order value for customer 1001 would be 5.

                   

                  When I create a summary field that functions well, too. But the summary gives me only the correct sum value for every customer when I switch off the filter.

                   

                  Either there is a way to include the colums in the summary view or the summary field is shown in my table view (prefered).

                   

                  Best wishes,

                  Oli /b[/quote]

                  • Sum over cells concerning the same number
                    Oli _

                    Hi Grant,

                    thanks for your help. I was succesfull in summing the order values. Now I have the problem in the summary view with the summed order-value for the customer.

                     

                    That is definitely my main problem. My Solution is now,

                     

                    1.) Filter the customer numbers (then ascending). Copy->Paste to Excel.

                     

                    2.) For the  sum of order values for every customer:

                    Switch off the filter go to the summary view (ascending over customer number) 

                    Copy->Paste to Excel.

                     

                    Is it possible to combine a summary colum to the table view and exclude the “old”  single order values and replace them with the summary results for every customer within Monarch ?

                     

                    Is there a way to show this Information in Monarch combined as a table ?

                    Perhaps I have to change my model. The model is made in type: detail.

                     

                     

                    To your questions with open items, as you supposed it is fixed:

                    I don’t have any problems with this figure. Because the figure is fixed for one customer. So I only need a summary of the order-values.

                     

                     

                    Best wishes

                    Oli

                     

                    [size="1"][ August 09, 2004, 05:19 AM: Message edited by: Oli ][/size]

                    • Sum over cells concerning the same number
                      Grant Perkins

                      Oli,

                       

                      Are you seeking to see the information for the selected customers with the figures for ALL customers?

                       

                      Some ideas ...

                       

                      If you create 2 summaries which are the same you can make one filtered and the other not filtered. If you have V7 Pro you can easily export both summaries in one process and to the same Excel Workbook and maybe the same worksheet.

                       

                      OR

                       

                      If you have a PRO version, you could produce the ALL CUSTOMERS summary and export it to an external file and then create a link to read the value(s) in to your table records as a fixed value (or values).

                       

                      Do either of these suggestions help? I am having some difficulty try to make a mental picture of what you need to achieve.

                       

                      Is there any possibility of providing a sample file and a document that shows what the results have to look like?

                       

                      Grant

                       

                      Originally posted by Oli:

                      Hi Grant,

                      thanks for your help. I was succesfull in summing the order values. Now I have the problem in the summary view with the summed order-value for the customer.

                       

                      That is definitely my main problem. My Solution is now,

                       

                      1.) Filter the customer numbers (then ascending). Copy->Paste to Excel.

                       

                      2.) For the  sum of order values for every customer:

                      Switch off the filter go to the summary view (ascending over customer number) 

                      Copy->Paste to Excel.

                       

                      Is it possible to combine a summary colum to the table view and exclude the “old”  single order values and replace them with the summary results for every customer within Monarch ?

                       

                      Is there a way to show this Information in Monarch combined as a table ?

                      Perhaps I have to change my model. The model is made in type: detail.

                       

                       

                      To your questions with open items, as you supposed it is fixed:

                      I don’t have any problems with this figure. Because the figure is fixed for one customer. So I only need a summary of the order-values.

                       

                       

                      Best wishes

                      Oli /b[/quote]