Colouring a Pivot table

[#1]

Hi

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

[oscar_ortiz]

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.

[#3]

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.

[#4]

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

[#5]

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

[#6]

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

[#7]

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

Try instead

rangesum(COUNT(id), 0)

[#8]

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

[#9]

Sorry, I meant

rangesum(COUNT(id), 0)