Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER 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

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

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

Creator III
Creator III

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

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?

MVP
MVP

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

MVP & Luminary
MVP & Luminary

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

Do you mean like this ?

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

Q1 - Like This.png

Not applicable

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

Not applicable

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

Not applicable

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