Announcements
cancel
Showing results for
Did you mean:
Creator II

## sum of rows in pivot when using column()

Hi All

I know help says "Pivot table totals are always calculated as expression total."
But maybe someone findout workaround for this so I don't need to change expression:

Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0

to huge sums with aggr?

Each of column is set analysis with variables.

Simple example to play with attached.

1 Solution

Accepted Solutions
Master II

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

10 Replies

Hello Karolina,

What are you expecting and where we need to work.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

I would like to have total for last column with expression Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  to be SUM OF ROWS not total of expression
I want to keep Column() function, not rewriting it to huge expression

Master III

Here i am not understanding about >>> " Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0  "

Creator II
Author

Master III

Convert this pivot chart  into straight chart >> Expression > Total Mode >set as Sum

Creator II
Author

Question is about pivot. I know straight table can do this - I even post a screen of it to show needed value.

Master II

try this expression:

=sum(aggr(sum(aggr(sum(F3),F5,F6))-sum(aggr(sum(F4),F5,F6))-if(sum(aggr(sum(F2),F5,F6)),(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))

-sum(aggr(sum(F2),F5,F6))/(sum(aggr(sum(F1),F5,F6))

-sum(aggr(sum(F2),F5,F6))))*sum(aggr(sum(F1),F5,F6))

,0),F5,F6))

it will not work using column():

Column total in pivot table not summing properly

Creator II
Author

In my org app I use sums with set analysis and variables so such expression would be quite big.

I also display part of this expression in different columns for example Column(3)-Column(4) is column(7).

If sum of rows would be somehow possible in pivot I would use simple Column(14)-Column(11)

if not I will need write expression with all sums and aggrs

Master II

i guess you will have to write the expressions with sum and aggr.

As Sunny mentioned in this post, column function is not working in this case!

hope this helps

Community Browser