Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with three expressions. I would like to sum together the result of these three expressions into a new 4th expression. Is there a way to pull the results from the first three into the new expression, or do i just have to replicate the first three computations inside the new expression?
for example:
A+B=C
X-Y=D
K(5)/X=M
I would like to learn how to write an expression C+D+M.
Or is there a better way to get it into the pivot table other than an expression? Be gentle I am just an accountant. I see some of the questions some of you post and it looks like hieroglyphs.....and very impressive ones at that.
Thank you in advance for any replys.
Matt,
In first place, if your expression label has special characters (in this case, the space in Total Value), you must use []
For example: [Total Value]+[Overhead]
In second place, you can't use Sum([Total Value]), because [Total Values] is only a value, the result of the expression in this cell of the table. You only can use the label (or column()) in arithmetic operations, or with OTHER expression.
For Example: [Total Value] + Avg(Tax)
Hi john,
You must put a Label of each expression. Then, in fourth expression, use the names:
Expression1: Sum(field1)
Expression2: Avg(field2)
Expression3: Count(field3)
Expression4: (Expression1/Expression2)+Expression3
Remind to use exactly the same labels.
use column(1)+column(2)+column(3)
Use the names. If you change the expression order, you will must modify the expression4
Hi John,
Follow Sebastian suggestion. Give particular name to each expression and just use that name in the 4th expression like
Exp1+Exp2+EXP3. If you use Column then if you change the order it will reflect in the result of 4th expression.
Still having difficulty (probably missing something very basic)
Expression1 label is Total Value and definition is Sum ([Value])
Expression 2 label is Overhead and definition is Sum ([Overhead])
when I try to add the expression (exactly as it reads here): Total Value + Overhead there is no result. It does not indicate an error or bad field name, just returns a result of "-" (the correct result should be a positive whole number).
I tried a simpler formulae to troubleshoot
if I add the expression sum ('Total Value') to try to see if I could just pull in the result from just the first expression, it returns a zero. By adding quotes to the label it changed the result from a "-" to a zero, but still not the correct positive number.
use coloumn function
i.e coloumn(1)+coloumn(2)+coloumn(3)
I'm sorry I am not able to get that to work either.....just returns "-"
I feel like I am getting the syntax wrong by omitting a [ or ' or some other little thing
Here is an example of what srikanthsri1 suggested. Using the columns expression is easiest - I can never seem to get the titles to work right but maybe that's just me.
Success with the column expression. I would rather use the expression labels so I don't have to change the expression if i move columns......but for now I am tired of messing with it.
Thank you VERY much for sending the test file. That made all the difference.