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 atNewbutton at standard toolbar or fromFilemenu clickNew. When data load wizard is showed, just cancel it.Before create the chart is necessary load data sample. To do that, pressCTRL + Eto 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 pressCTRL + Rto 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. FromNew Sheet ObjectselectChartand follow the instructions below.
1. By default, bar chart is selected. Just go to next window clickingNextbutton.
2. OnDimensionwindow click Month/Year as your dimension andAddit toUsed Dimensionsside.
3. ClickNextbutton 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 onFinishbutton.
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 selectingProperties. A lot of tabs are available, but we will use justExpressiontab. 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 usingRGBfunction. For example,RGB(255, 0, 0)is red, whileRGB(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 troughIFfunction, 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 usingIFfunction, need to be defined atBackground Colorproperty throughDefinitionfield. First of all,IFfunction will verify a value for each bar using=Sum(Accidents). After, the high value needs to be identified. For that, theMaxfunction is used to get max value from a memory table created byAggrfunction. See use ofTotalkeyword that will ignore any dimension value from chart. WhenAggris 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])).
When the maximum value is found, theRGB(red, green and blue) is used to define the color. Otherwise, anotherRGBis 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 newIFfunction to evaluate what is value to be painted, highest or lowest. The following code is used to this.
Just to organize data sequence, go back to chart's Properties and selectSorttab. Ensure that first field is[Month/Year], otherwise usePromotebutton to change it. After that, uncheck all items onSort byoption and, selectLoad Order = Originalfor[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.
FromGeneraltab fill someWindow TitleasCar Accidents by Month.
At same tab, fillShow title on chartwith something likeAccidents each 1.000 vehicles at road.
Click onTitle Settingsbutton and align title toleftusingHorizontal Alignment.
FromTitle Settingwindow, clickFontbutton and change its font-size to 10,Regularstyle.
Press Ok how many times is needed to go back toProperties Window. Goto Expression tab and selectValues on Data Points.
GotoAxestab and selectHide Axes.
ActivateLayouttab and set0 pt(zero) toBorder Withoption.
ActivateCaptiontab and uncheck bothPrintandSend to Exceloptions.
At same tab, change to white colorBackground Option, eitherInactive CaptionandActive Captionitens.
When you finish all changes you want, addMonth/Yearfield 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.