2 Replies Latest reply: Apr 5, 2013 5:17 AM by christian juillard RSS

    Total with Aggr...grrr!

    christian juillard

      Hi everybody

       

      I hope someone can really help me with this.

      On the joined qvw you'll see a pivot table with some column using sum(aggr(sum(field), dim))

      The GTP column compute the total according to some other columns' expressions.

      But what I want is the sum of the lines. In my example the total is 15 and should be 20.

       

      I'll be grateful forever if you do so.

       

      best regards

      chris

        • Re: Total with Aggr...grrr!
          Henric Cronström

          First of all, if QlikView calculates the total to 15, then it means that the formula indeed evaluates to to 15, when applied over all records in the database. It does not just sum the lines in the pivot table.

           

          I was not able to follow all your calculations, but I think you use aggr() when you don't need to. For example, you use the following formula to calculate [Coût msg]

           

            sum(

               aggr(

                  (sum([Qté msg] * Ref_CoutMsg / Ref_TxChange)),

                  $(vFldRecrut)

                  )

               )

              /

                    sum( [Qté msg] )

           

          when I think you should simply use

           

                    sum([Qté msg] * Ref_CoutMsg / Ref_TxChange) / sum([Qté msg])

           

          Remember that Aggr() is used for nested aggregations - the first level of aggregation is done implicitly by the sum() function. So, bottom line is that I think you need to re-write your expressions not using Aggr().

           

          However, if you think you need Aggr() in your expressions and you in addition want the total to be the sum of rows and not the expression over all records in the data, then you could switch to straight table anc choose "Sum of Rows" as total mode (on the expressions tab).

           

          HIC

            • Re: Total with Aggr...grrr!
              christian juillard

              Hi Henric,

               

              thank you for your answer but the fact is I do need to use aggr to compute some totals otherwise the total is false. I agree may be sometimes i may have used aggr in a formula where it is not mandatory. And,unformtunately, I can't use straight table, it would have been so good.

               

              best regards

              christian