Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table column calculation

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

9 Replies
MK_QSL
MVP
MVP

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

Anonymous
Not applicable
Author

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

2014-07-21_10-54-46.png

Not applicable
Author

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
MVP
MVP

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Do you mean like this ?

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

Q1 - Like This.png

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Forgot to attach the how the output looks like. Final Output.jpg