Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Colouring a Pivot table

Hi

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

untitled.bmp

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

8 Replies
oscar_ortiz
Valued Contributor

Re: Colouring a Pivot table

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.

Not applicable

Colouring a Pivot table

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.

Not applicable

Re: Colouring a Pivot table

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

Not applicable

Re: Colouring a Pivot table

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

Not applicable

Re: Colouring a Pivot table

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

Not applicable

Re: Colouring a Pivot table

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

Try instead

rangesum(COUNT(id), 0)
Not applicable

Re: Colouring a Pivot table

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

Not applicable

Re: Colouring a Pivot table

Sorry, I meant

rangesum(COUNT(id), 0)

Community Browser