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

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
Partner - Specialist
Partner - Specialist

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
Author

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
Author

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
Author

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
Author

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
Author

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

Try instead

rangesum(COUNT(id), 0)
Not applicable
Author

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
Author

Sorry, I meant

rangesum(COUNT(id), 0)