7 Replies Latest reply: Feb 14, 2017 3:24 PM by Stefan Wühl RSS

    Incorrect SUM value

    Andrew Moberg

      I have a value which is supposed to SUM room minutes, however I cannot get the value to line up with the manual calculation.

      Here is my table model:

      Tbls.png

       

      Here are the individual values for the room minutes:

      Mins.png

      The sum of this is 1880

       

      Here is what I get when using the SUM function:

      The value represented from this formula is 1970...?

      sum.png

       

      Why is the SUM value incorrect?

        • Re: Incorrect SUM value
          Sunny Talwar

          What you are seeing is the distinct values within Room Minutes field, but there might be multiple values for the same number. Sum([Room Minutes]) will sum all values. Not a good idea, but Sum(DISTINCT [Room Minutes]) will give the sum of distinct values within Room Minutes field. Is that what you want?

          • Re: Incorrect SUM value
            Anil Samineni

            May be use this in text box and then see in Straight/Pivot

             

            Sum([Room Minutes])

            Or

            Sum(TOTAL [Room Minutes])

            Or

            Sum(Distinct [Room Minutes])

              • Re: Incorrect SUM value
                Robin Hausdörfer

                I think "90" is not part of the selection "HILLCREST MAIN OR"

                  • Re: Incorrect SUM value
                    Andrew Moberg

                    I selected each ID manually and it appears the 90 minute case is being summed twice. Not sure how or why...?

                     

                    test.png

                      • Re: Incorrect SUM value
                        Robin Hausdörfer

                        try Concat([Room Minutes],' | ')


                        as expression for debugging

                        • Re: Incorrect SUM value
                          Aar Kay

                          Add Dimensions to the straight table from the tables CALBLOCK and STGII, you will be able to see where the data is duplicating. If still cannot find the values on which the expression is duplicating try adding dimensions from CASES table as well.

                          • Re: Incorrect SUM value
                            Stefan Wühl

                            It seems to me like your issue can be explained only looking at the CASES table, since both the dimension and expression field originate from that table.

                             

                            What is the primary key of that table? Is it [Case No], i.e. is there only a single record per case no?

                            Or is it [Log ID]? Or a combination of fields?

                             

                            Try to add the primary key to your straight table as dimensions.

                             

                            Another way to investigate could be to check your data source and script. Are you using JOIN in your script related to table CASES? A JOIN may unintentionally duplicate records.

                             

                            After all, maybe the value shown is indeed correct, so we might need also to discuss the way you've calculated the sum manually. How have you done it? As Sunny pointed out, a list box or table box will only show distinct combinations of values, it will not show duplicate records or sets of identical values.