5 Replies Latest reply: Mar 31, 2010 6:37 AM by jbotha RSS

    Total sum in Pivot

    Daniel Göhlin

      Hi, I've been reading the forum all night long, now I'm tired so maybe someone can help me out with this pivot table. I can't get the total sum to calculate right. The sum should be 304065.

      I Use following code to do the calculation. I tried to use the aggr function but I get the same result :( I would be more than happy if someone could give me a hint. Thanks!

      =IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

       

      error loading image

        • Total sum in Pivot
          bglbi

          Try changing your calculation:

          from this:

          IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

          To this:

          Sum(If([Curr] >"", [Curr]*[Unddel_ordervalue],[Unddel_ordervalue]))

           

          -Ed

            • Total sum in Pivot
              Daniel Göhlin

              Unfortunatly, that was not the solution, I think it has to be someting with the aggr function....

              Well the result from your solution (depending if I use '' or "":

              Thanks anyway!

                • Total sum in Pivot
                  Johannes Sunden

                  Hi danne31,

                  There is a chapter on this in the reference manual. "Sum of rows in a pivot table" or something like that in the back somewhere.

                  The if statement in the expression can make the dimension independent total be something else than the sum of rows. Still correct, but not what one would like to see. In that case you can aggregate the sums for each dimension value and then sum these up for the total.

                  if the original expression was: =IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue]))

                  You should be able to write: =sum(aggr(IF([Curr]>'',SUM([Curr]*[Unddel_ordervalue]),Sum([Unddel_ordervalue])), Kund_beskrivning))

                  However, I see that you have more than one dimension hidden under the pivot expansion icons so you might want to add these dimension fields to the aggr function. This is all better explained in the reference manual so I suggest you look up the chapter I'm referring to.

                  cheers,

                  Johannes