6 Replies Latest reply: May 6, 2012 1:42 AM by Anosh Nathaniel RSS

    Expression Total not matching to row values

      Hi,  for example if i take the the calulcation coloum3/coloum1 i get the correct values on a row by row basis , yet when using the expression total functionality the result is different to the physical caluclation.

       

      For example

       

      5968.16 / 1808.2 = 3.30061

       

      Yet in QV the expression total result is 3.63

       

      Our actual expression is

       

       

      =

      num(sum( {$< tcat={0} , [Finalised Date]={"<=$(=_vNull_Fin_Date_Excl) "} , _Year={"$(=MAXSTRING(Year))"} , _sequence={"<=$(=Max(Sequence))"} >} DelayCount )

      /

      sum( {$< tcat={0} , [Finalised Date]={"<=$(=_vNull_Fin_Date_Excl) "} , _Year={"$(=MAXSTRING(Year))"} , _sequence={"<=$(=Max(Sequence))"} >} EntryCount

      ) , '#,##0.0' )

        • Expression Total not matching to row values
          Stefan Wühl

          There can indeed be a difference between an expression total and a sum-of-rows total.

          For example, if your measure values might be included / accounted to different dimension values, you will get differing results.

           

          Have you tried using the total mode sum-of-rows in a straight table or calculating a sum-of-rows using aggr() function in a pivot table (there is a chapter in the Help about latter method)?

          • Expression Total not matching to row values
            Jonathan Dienst

            Hi

             

            The difference in sum of rows and expression typically happens when you have something like a ratio (as in your example), that is not additive. For example, consider two data points:

             

            1/3 and 2/3

             

            When added together you will get 1/2+2/3 = 1, but when calculated with the expression, you will get (1+2)/(3+3) = 1/2. The expression is mathematically "correct", and is not arithmetically the same as the sum of rows.

             

            Hope that helps

            Jonathan

            • Expression Total not matching to row values
              Anosh Nathaniel

              Hi,

               

              If the word total occurs before an expression, the calculation will be made over all possible values given the

              current selections, but disregarding the chart dimensions.

               

              Where are you using total keyword? Is it in column 1 which you use to divide column 3.

              Please explain in detail.

               

              Thanks,

              Anosh

              • Expression Total not matching to row values

                Thanks all , i'm making progress ..... for the final step i need to add a calculcation against two expressions in my existing table which consists of many rows of data. Example being

                 

                Hrs (expression total)  = 20

                 

                delay (sum of rows) = 100

                 

                 

                Total Delay / Expression Total Hrs =  5

                 

                Is this possible, if so could i get a steer on possible syntax  ?

                 

                I'm using a simple expression =([delay]/hrs) and am struggling to obtain the total average of 5 and instead can only obtain a sum of rows for the caclulation, when i select expression total it is null.

                 

                 

                 

                kr

                • Re: Expression Total not matching to row values

                  Thanks all , i'm making progress ..... for the final step i need to add a calculcation against two expressions in my existing table which consists of many rows of data. Example being

                   

                  Hrs (expression total)  = 20

                   

                  delay (sum of rows) = 100

                   

                   

                  Total Delay / Expression Total Hrs =  5

                   

                  Is this possible, if so could i get a steer on possible syntax  ?

                   

                  I'm using a simple expression =([delay]/hrs) and am struggling to obtain the total average of 5 and instead can only obtain a sum of rows for the caclulation, when i select expression total it is null.

                   

                   

                   

                  kr