topic Re: Colouring a Pivot table in QlikView

Colouring a Pivot table

Fri, 25 May 2012 13:06:45 GMT

I'm hoping someone can help me. I a pivot table and I would like to colour a part of it red.

I have week numbers along the top and I would like to coulour anything that is 18 and over. I can get it to do it if there is a value, But I would like it to do it regardless of the value.

Hope this makes sense.

THank you in advance.

Simon

Re: Colouring a Pivot table

oscar_ortiz — Fri, 25 May 2012 15:43:26 GMT

I'm going under the assumpition that your RTT_Value field is a dimension. Go to the dimensions tab click on the "+" sign next to your field and use an expression like this for your background color.

=If( Value >= 18, LightRed(), White() )

See attached example.

Colouring a Pivot table

Fri, 25 May 2012 16:28:43 GMT

Your formula works with QV 10 SR 4, but not QV 11 SR 1 !

I think it's a bug in QV 11, because it works if the dimension is vertical, but not horizontal.

Re: Colouring a Pivot table

Mon, 28 May 2012 13:12:45 GMT

Thank you Oscar for your reply, but your example appears to work due to the fact that Qlikview is not regarding the 0 as missing.

When I pull the data in from a spreadsheet, Qlikvire regards the blanks as missing, ands its these ones that I can get highlighted.

I've attached my document which may help.

THank you

Simon

Re: Colouring a Pivot table

Mon, 28 May 2012 13:26:59 GMT

Hi Simon,

You can try following expression which is slight modification of Oscar's expression:

=If( Value >= 18 or len(Value)=0, LightRed(), White() )

I am not sure about its behaviour in QV 11.

Hope this helps.

Regards,

Neha

Re: Colouring a Pivot table

Mon, 28 May 2012 14:59:24 GMT

Hi Everyone,

Thank you for your responses, I have just read a post which I think answers my question.

Apparently you can't colour the background of a cell that has a missing value:

Background color for pivot table cells with MISSING values

To get my counts I am counting an ID field, is there a away to replace a missing count with a value of 0?

My expression is this:

COUNT(id)

I've tried this but it didn't work:

IF(COUNT(id)=0,0,COUNTid))

Any ideas would be greatly appreciated

Cheers

Simon

Re: Colouring a Pivot table

Mon, 28 May 2012 15:02:02 GMT

COUNT(id) will return "null()" if there is no value.

Try instead

rangesum(COUNT(id), 0)

Re: Colouring a Pivot table

Mon, 28 May 2012 15:59:43 GMT

Hi,

COUNT(id) returns missing, I know this by changing the symbol under the prestation table for missing and seeing where it appears in the pivot.

rangemax(COUNT(id), 0) didn't change anything.

But thank you for the quick response.

Cheers

Re: Colouring a Pivot table

Mon, 28 May 2012 16:13:34 GMT

Sorry, I meant

rangesum(COUNT(id), 0)