Pivot table column calculation

[#1]

Hi,

I have a Pivot table that shows data as per attached. AAA, BBB, CCC are part of Portfolio column. The number of columns will vary, which means I can have something like AAA, BBB or AAA, BBB, CCC, DDD. Now AAA is my model column (Sequence is always 0). I want to colour code the cells BBB through CCC such that if BBB-AAA < 0 then colour Red else colour Blue, Same applies for CCC-AAA. Hope that makes sense.

Cheers,

Prasad

[MK_QSL]

Kindly provide sample data for this question. Easy to work..

[Anonymous]

Expand on each Dimension and/or equation and double click the attribute such as Background Color.  Then put your conditional statement.

[#4]

Thanks Michael, I know the Background Color  property that's fine. What I am struggling with is the expression. So in a way I need to be able to use AAA as a base value for computation.

Manish do you want me to attach the QVW?

[MK_QSL]

Hi, Kindly load your sample apps.....would love to work on the same...

[rwunderlich]

Is Portfolio a dimenssion? If so you can code the expression to calculate the difference as:

sum(Value) - sum({<Portfolio={AAA} TOTAL Value)

And then use that in an if() to set the color.

-Rob

[#7]

Do you mean like this ?

I'm afraid you have to set it manually per column.

[#8]

Portfolio is an expression. I will load the sample file.

[#9 · Accepted solution]

Thanks all. I finally managed using the following expression in the Background Color property of the Portfolio expression.

if(num(isequence) <> 0 and max(isequence) = min(isequence),
if(
Round((Column(1)-First(Column(1))),0.01)> vMax
, rgb(170, 200, 225)
, (if(Round((Column(1)-First(Column(1))),0.01)<vMin, rgb(255, 150, 100), rgb(255, 255, 255))
)
)
)

I first check that iSequence (order of the column that I have in my QVD) is not equal to 0, this is because I don't want to apply any color logic to the first column. Then I Subtract the 0th column value from every column and if it is greater than vMax (variable that stores max tolerance level) then color it cyan else if it is less than vMin(min tolerance level) then color it orange.

[#10]

Forgot to attach the how the output looks like.