Pivot table calculations

[#1]

Hello,

I want to change the color of cells in the pivot table below (example) if the value exceeds the value of the previous year.

I tried the use of aggr, above, column etc, but I can not find the right formula.

Can anybody help with this?

		Year	2009	2009	2010	2010
Category	Color	Label	excl	incl	excl	incl
bike	blue		12	15	13	16
bike	orange		14	17	14	17
bike	red		13	16	13	16
car	blue		45	50	50	55
car	orange		30	35	32	35
car	red		50	55	50	55

regards, Meike

[MayilVahanan]

Hi,

     In expression, Click '+' sign , in background color use like this

     =if(Column(1) - Column(3) > 0, blue(), Green())

     or

       =if(Column(1) - Column(2) > 0, blue(), Green())

     i'm not sure of that column number, how many expression you're using. and what are the dimension?

     Can you say in detail, or sample post?

     Hope it helps

[#3]

Thanks for your reply.

Herby I added the example.

I dont think formula will work.

[CELAMBARASAN · Accepted solution]

Try with this expression(as Background color expression)

=If(Before(TOTAL Sum(Costs), 2) < Sum(Costs),RGB(255,0,0))

Hope it helps

[jonathandienst]

Hi

If the Year is a pivoted dimension, then

=If(Before(Column(1)) > Column(1), red(), green())

Its possible that Column(1) wont work, then use

=If(Before(expr) > expr, red(), green())

Where expr is the chart expression

Hope that helps

Jonathan

[#6]

Thanks it work!

[#7]

Hello,

can someone tell me if a PivotTable is possible make calculations per row?

eg:

row 1: sum(F1)

row 2: max(F1)

row 3: avg(F1)

row 4: Sum(row 1 + row 2)

[CELAMBARASAN]

Ya you can do it

with RowNo() function

Pick(RowNo(), Sum(F1),Max(F1),Avg(F1),RangeSum(Sum(F1),Max(F1)))

Hope it helps

Celambarasan

[#9]

But if I select a row in the pivot table it shows me always the first row value.

[#10]

Thanks for the help.

Have you found a way to do this.

example: Pick (RowNumber, Sum (Sales), Avg (Sales), Max (Sales), Min (Sales), sum (Sales)-Min (Sales))