Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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():
Hello Karolina,
What are you expecting and where we need to work.
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
Please share expected output...
Here i am not understanding about >>> " Column(3)-Column(4)-if(Column(2),(Column(5)-Column(6))*Column(1),0 "
Convert this pivot chart into straight chart >> Expression > Total Mode >set as Sum
Question is about pivot. I know straight table can do this - I even post a screen of it to show needed value.
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():
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
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