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 above.
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 [
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
2. On Dimension window click Month/Year as your dimension and Add it to
Used Dimensions side.
Next button to advance to expression window. This place is used to enter a formula to calculate bars on chart. Enter the following code:
4. Just close the wizard clicking on
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 above. 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
Total keyword 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:
=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 byoption and, select
Load Order = Original for
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 Titleas Car Accidents by Month.
- At same tab, fill
Show title on chartwith something like Accidents each 1.000 vehicles at road.
- Click on
Title Settingsbutton and align title to
- From Title Setting window, click
Fontbutton and change its font-size to 10,
- 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
- Activate Layout tab and set
0 pt(zero) to
- Activate Caption tab and uncheck both
Send to Exceloptions.
- At same tab, change to white color
Background Option, either
- Uncheck also
Fontbutton and change its
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.