3 Replies Latest reply: Sep 25, 2013 4:06 PM by Oleg Troyansky RSS

    Total SUM

    Ciro Petrullo

      Hello I have a problem with qlik and when I put the partial sums of the system.

      The problem is the following

       

      D323/09/201324/09/2013Total
      D1D2V1V2V3V3/V1V1V2V3V3/V1V1V2V3V3/V1
      BA17161588.2%87450.0%25231976.0%

       

       

      The system total show 76%, it's normal, but I need (88.2+50)/2= 69.1

       

      My solution is add a column whit this (before([V3/V1]) +[V3/V1]) /2, but is not good for me.

       

      You have suggestions?

        • Re: Total SUM
          Jean-Luc Pache

          Qlikview give the correct answer: 19/25=76%.(sum(v3)/sum(v1))

          In statistic you cannot calculate the average of percentages.

            • Re: Total SUM
              Ciro Petrullo

              thanks for the reply.

              I know it's a correct answer, but there is a way to get the result by adding a field without using before?

                • Re: Total SUM
                  Oleg Troyansky

                  If I understand you correctly, QlikView calculates the "Expression Total" at your subtotal levels and you'd like to calculate the average of the subtotals instead...

                   

                  You can use Advanced Aggregation (funciton AGGR) to pre-calculate the subtotals at the relevant level of detail, and then to apply average to the pre-calculated results: something like this:

                   

                  AVG( AGGR ( sum(V3)/SUM(V1), D1, D2) )

                   

                  Usually the same expression should work equally well for all levels. If it doesn't, then you can use Dimensionality() to define what calculation to use at what level:

                   

                  IF (Dimensionality() = 0, <Formula1>, <Formula2>)

                   

                  cheers,

                   

                  Oleg Troyansky

                  www.masterssummit.com