Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How Do I set different background colors based on column label and value within that column?

Ok, so I'm new to Qlikview Sense so bear with me.

Basically, I've got a pivot table that looks like this:

Now, what I need to do is set conditional formatting on the column header.  For example, for "Basic DL" my "Green" condition might be values less than 950, but on "Basic Titles" my "green" condition might be values less than 900.  I've played around with the background color conditions and expressions (ColumnNo, Column, Lookup, etc), but I just cannot get the right combination of functions to get this right.  Any thoughts or help would be greatly appreciated.  Please note this is for Qlikview Sense Desktop.

13 Replies
arulsettu
Honored Contributor III

Re: How Do I set different background colors based on column label and value within that column?

can u share the app

Employee
Employee

Re: How Do I set different background colors based on column label and value within that column?

Hi,

Someone can correct me if i am incorrect however you can't colour code column headers in the pivot table. You can colour code the rows - measures -> background colour expression. I would suggest using the KPI object above the Pivot table. Add a KPI for each expression. You can then control the colour coding of the KPI object.

arulsettu
Honored Contributor III

Re: How Do I set different background colors based on column label and value within that column?

you are correct we cannot color the column headers

Not applicable

Re: How Do I set different background colors based on column label and value within that column?

In Qlik Sense, under data and the measure in question, you have the option to setup a background colour expression.

You could have, for example:

If([Basic Titles] <=950, LightRed(), If([Basic Titles] >=950, Yellow()))

You could then apply alternative expressions to each measure.

Regards,

Michael

Not applicable

Re: How Do I set different background colors based on column label and value within that column?

yes, you are correct but not impossible. He can make a custom extension for that.

jayaseelan
Contributor III

Re: How Do I set different background colors based on column label and value within that column?

Try this,

if(column(number)<.50,RGB(255,0,0),Green())

Column(n)-  means should mention the column number.

Not applicable

Re: How Do I set different background colors based on column label and value within that column?

That would work if every column was a different measure.  But they're all the same - AVG(Duration). 

It's almost like I need some kind of nested "IF" statement that references the column label for that column.  Something like

IF(column_label="Basic DL")

     THEN (if (AVG(Duration)>950,red(),green)

IF(column_label="Basic Titles")

     THEN (if (AVG(Duration)>900,red(),green)

I get that the syntax is all screwy here, but you get the point. 

Not applicable

Re: How Do I set different background colors based on column label and value within that column?

This doesn't work because it applies the formatting to all of the columns.  Basically its saying IF column(1) is less than .5 then apply a red background to all cells in the pivot table.  What I need is some kind of nested IF structure that references the column header/label.

Not applicable

Re: How Do I set different background colors based on column label and value within that column?

Right, I totally understand that.  I'm not trying to color the headers, but the actual backgrounds of the pivot table cells.