Saturday, February 21, 2009

Highlighting a Chart Value


I was asked, “How do you highlight a null value in a straight-table chart?” If you want to set the background color or text color based on a range of expression values then the Chart Properties->Visual Cues is the way to go. But the Visual Cues tab doesn’t work well for null values and it won’t help if you want to highlight the value based on anything more than a simple test of values. For that, you want to use a separate, special expression to set the background color or text color.

Right-click on the chart and select Properties and go to the Expressions tab. Click on the little plus sign ( + ) to the left of the expression you want to highlight. See the example in the picture above. In the example, the expression is named Shipments and I have clicked on the Background Color choice and entered this expression into the box:
  If(IsNull(Shipments),LightRed())

That expression tells the chart to use the LightRed color as a background color when the Shipments value is null. There are other colors you could use – search on color functions in the Help text to find information about other colors. Background color works well to highlight values in a straight table chart but it won't help with line graphs or bar charts. The expression can include other factors like the source of the data or the value in the chart dimension or the average monthly sales volume for 2008; use whatever factors that provide a reason for pointing out that particular expression value.

3 comments:

Anonymous said...

Additional quicktip - after opening the Background Color property, you can use the Colormix Wizard, which is available under the "File" menu. This can be useful for highlighting exceptions or outliers, for example.

Antoine said...

Hi,

I used your example in a pivot table. Works fine but the column title take the same color than the first table line. Do you know how to avoid that?

Thank you.

-TB said...

Hi Antoine,

I don't know any way to avoid that. I've tried changing the expression to avoid coloring the column title but nothing worked. At one time I sent an email to QlikTech support and they responded that it is "working as designed".
I've seen at least one posting on the QlikCommunity forum asking about the same issue but nobody had a useful suggestion.