topic Pivot Table - Colouring specific fields in QlikView

Pivot Table - Colouring specific fields

Wed, 04 Aug 2010 01:05:15 GMT

I have a pivot table that is really confusing to read, but the data on it is valuable. I think that I can make it better, if I could colour code the specific cells.

Without going into too much irrelevant detail, the graph describes how the customers break into sales deciles, and how those deciles change over time.

What I'd like to do is code the chart to clearly indicate which is an improvement, which is a downgrade, and which is no change.

The way I would describe it is:

If RowNo() = ColumnNo() Then Neutral (gray, white, something neutral, whatever)

If ColumnNo() > RowNo() Then Bad (red, orange, shades of red, whatever)

Else Good (green, etc)

Can't do it with 'visual queues', so I'm right clicking in the table, and selecting 'Custom Format Cell'; Then attempting to use RowNo and ColumnNo functions to change the colour, but it appears that RowNo() is always = 1.

So my guess is that this formatting has no idea about the table to which it is applied.

Anyone know if this is possible?

Pivot Table - Colouring specific fields

johnw — Wed, 04 Aug 2010 01:13:19 GMT

If I'm understanding the question, I'd think you'd just use a background color expression for the expression. For instance, here's how I color code some data in one of my applications red, yellow or green:

if(Capped/Target<.5,rgb(255,200,200) // red
,if(Capped/Target<1 ,rgb(255,255,200) // yellow
,rgb(200,255,200))) // green

So I'd think you'd just do this:

if(rowno()=columnno(),white()
,if(rowno()<columnno(),rgb(255,200,200) // red
,rgb(200,255,200))) // green

Yep, just tried it. Works like a charm, assuming I've understood your question.

Pivot Table - Colouring specific fields

Wed, 04 Aug 2010 18:46:23 GMT

There I go again... overcomplecating things.

Thanks again!