topic Re: Pivot table column calculation in QlikView

Pivot table column calculation

Mon, 21 Jul 2014 15:23:40 GMT

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

Re: Pivot table column calculation

MK_QSL — Mon, 21 Jul 2014 15:29:23 GMT

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

Re: Pivot table column calculation

Anonymous — Mon, 21 Jul 2014 15:35:23 GMT

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

Re: Pivot table column calculation

Mon, 21 Jul 2014 15:55:14 GMT

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?

Re: Pivot table column calculation

MK_QSL — Mon, 21 Jul 2014 20:38:12 GMT

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

Re: Pivot table column calculation

rwunderlich — Mon, 21 Jul 2014 22:56:24 GMT

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

Re: Pivot table column calculation

Tue, 22 Jul 2014 03:50:46 GMT

Do you mean like this ?

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

Re: Pivot table column calculation

Tue, 22 Jul 2014 09:54:51 GMT

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

Re: Pivot table column calculation

Tue, 22 Jul 2014 15:32:02 GMT

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.

Re: Pivot table column calculation

Tue, 22 Jul 2014 15:33:57 GMT

Forgot to attach the how the output looks like.