Introduction

Although color can be set using Color tab, sometime the best way to show a specific value for users is highlighting bar on charts through color formulas defined at expression level. It’s possible draw attention to high values with different colors than lowest values, letting users take decisions more fast. Generally the colors are used when more than one dimension is defined. While is possible let QlikView define the colors based on two or more dimensions automatically, when just one dimension is outlined too many colors will not help decision users. So, color is a important component when defining charts.

For example, light colors are used to general information, while strong colors draws attention from users to specific values or potential problems. Remember when red light is turned on at traffic light. It’s very important stop the car. As usual, strong colors can be used to highlighting bars chart as the picture at side.

This chart is showing how many car crash by month along some years, based on 1.000 vehicles passing through roads. Of course, as many accidents occurred more high the bar on chart is going to be. Sometime is hard to identify what is the month with more accidents, because two or more bars on chart has very similar values. That’s a good example to highlight some values. On the other hand, low values for this situation is good news. So, lower values can be colors more smooth.

To use this technique colors will be defined at Expression level and not in Color tab. In fact, colors at Color tab has no effect when applying this technique. If you want to test this scenario, just load data sample below and follow steps shown at next sections. If you are new in QlikView, make sure you have this product installed. This post was prepared using QlikView 11 SR12.

LOAD * INLINE [
"Month/Year", "Accidents"
jan/13,	'26,2'
fev/13,	'22,9'
mar/13,	'27,8'
abr/13,	'25,1'
mai/13,	'27,7'
jun/13,	'27,0'
jul/13,	'27,6'
ago/13,	'28,1'
set/13,	'27,4'
out/13,	'27,2'
nov/13,	'27,2'
dez/13,	'30,2'
jan/14,	'23,5'
fev/14,	'23,4'
mar/14,	'26,1'
abr/14,	'25,2'
mai/14,	'25,4'
jun/14,	'24,0'];

Don’t you have QlikView?

Creating the Bar Chart

Let’s create the bar chart using the same procedure used to create any other chart. Of course, first of all, open QlikView application from Windows operation system. Click at New button at standard toolbar or from File menu click New. When data load wizard is showed, just cancel it. Before create the chart is necessary load data sample. To do that, press CTRL + E to open QlikView Script Editor. Go to last row, press Enter to get some space and paste data sample as shown at previous section. Don’t forget to save your job before press CTRL + R to run the script. Now you’re back to main window and you’re abbe to create a new chart just right click at an empty place to see shortcut menu. From New Sheet Object select Chart and follow the instructions below.

1. By default, bar chart is selected. Just go to next window clicking Next button.

2. On Dimension window click Month/Year as your dimension and Add it to Used Dimensions side.

3. Click Next button to advance to expression window. This place is used to enter a formula to calculate bars on chart. Enter the following code:

=AVG(Accidents)

4. Just close the wizard clicking on Finish button.

Nice! You have created your chart and you’re able to see how many accidents has in each month. Of course a lot of design tricks are available to take your charts more legible and beautiful. Last section in this post will let you change some properties to redesign the chart’s interface. But, up to now it’s enough to learn how to highlight the bars on chart.

Highlighting the Bar Charts

Go back to the chart properties clicking with right button on chart and selecting Properties. A lot of tabs are available, but we will use just Expression tab. From that, open expression properties clicking at plus (+) sign as shown at picture at side. Take a look that, the background color is a property available for change colors at bar level. Colors can be specified using RGB function. For example, RGB(255, 0, 0) is red, while RGB(0, 0, 255) is blue. Valid range are 0 to 255 for each of parameter. But, to highlight the bar on chart is necessary to use a conditional formula trough IF function, because just one of them needs to be painted with different color.

In fact, QlikView needs verify what is the most high value that is showed and, just for it, change the color. So, the following formula using IF function, need to be defined at Background Color property through Definition field. First of all, IF function will verify a value for each bar using =Sum(Accidents). After, the high value needs to be identified. For that, the Max function is used to get max value from a memory table created by Aggr function. See use of Totalkeyword that will ignore any dimension value from chart. When Aggr is in place a memory table is created as using the Group By SQL statement. This function will sum values from accidents group by period through this formula: =Aggr(Sum(Accidents), [Month/Year])).

=If(Sum([Accidents]) = Max(Total Aggr(Sum([Accidents]), [Month/Year])) , RGB(245, 121,111), RGB(122,122,122))

When the maximum value is found, the RGB (red, green and blue) is used to define the color. Otherwise, another RGB is in place defining all other values. Therefore, only the most high value is painted as red while other values are defined as grey. Of course is possible define the lowest value with other color using the same technique. Just add a new IF function to evaluate what is value to be painted, highest or lowest. The following code is used to this.

=If(Sum([Accidents]) = Max(Total Aggr(Sum([Accidents]), [Month/Year])) , RGB(245, 121, 111), 
 If(Sum([Accidents]) = Min(Total Aggr(Sum([Accidents]), [Month/Year])) , RGB(111, 121, 211), RGB(122, 122, 122)))

Just to organize data sequence, go back to chart’s Properties and select Sort tab. Ensure that first field is [Month/Year], otherwise use Promote button to change it. After that, uncheck all items on Sort by option and, select Load Order = Original for [Month/Year] field.

Improving Chart’s Design

If you wanna change some properties to get a best design, go to chart’s properties and follow the tricks below.

• From General tab fill some Window Title as Car Accidents by Month.
• At same tab, fill Show title on chart with something like Accidents each 1.000 vehicles at road.
• Click on Title Settings button and align title to left using Horizontal Alignment.
• From Title Setting window, click Font button and change its font-size to 10, Regular style.
• Press Ok how many times is needed to go back to Properties Window. Goto Expression tab and select Values on Data Points.
• Goto Axes tab and select Hide Axes.
• Activate Layout tab and set 0 pt (zero) to Border With option.
• Activate Caption tab and uncheck both Print and Send to Excel options.
• At same tab, change to white color Background Option, either Inactive Caption and Active Caption itens.
• Uncheck also Allow Maximize and Allow Minimize options.
• Click Font button and change its size to 14.

When you finish all changes you want, add Month/Year field to interface and select in it some datas. Chart will change colors based on highest and lowest values. Your chart should be similar image below.

Used Functions

• If
• Sum
• Max
• Min
• Aggr
• Rgb
• Avg