Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]))
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
-Ed
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
Hi Johannes!
Perfect! Thanks a lot, it works just the way i want to!
/Daniel
Thank you Johannes
I have been pulling my hair out over this for almost 10 hours straight.