This post will show you how to add color to highlight the maximum and minimum values in your chart in both Qlik Sense and QlikView.
Observe the mighty mini-chart! Although it is humble in size, it features the ability to automatically highlight the highest and lowest values within the chart by simply checking the box. Wouldn’t it be great if normal QlikView and Qlik Sense visualizations had this option? Are you listening R&D?
Highlighting the highest and/or lowest value in your chart helps to identify your strongest and weakest point. This feature can add real analytical value and can help your dashboards look more professional.
Fortunately we can implement this feature in your charts in both tools. But you have to manually create the expressions that accomplish this for you. We will go through an example in both QlikView and Qlik Sense.
Highlighting the Minimum and Maximum Values in QlikView
We have created a bar chart that displays the sales by date. We would like the highest value (11/1) to be colored green and the lowest value (5/1) to be colored red. Here is the chart before.
In QlikView, the color of individual data points can be changed in Properties > Expressions. Expand the expression by clicking on the plus sign in the display window. We will enter an expression for Background Color
What we are basically saying here is if the value of the expression for a particular bar is equal to the maximum of all the expressions lined up by date, paint the bar green. If the value of the expression is equal to the minimum of all the values lined up by date, paint that bar red. All others get colored blue. There is an extra issue that needs to be handled for the minimum in this case. There are a few dates in the calendar that have no associated sales. When the expression calculates, the minimum of the aggregation in this case is actually zero, which does not correspond with any of the dates drawn in the chart. For this reason, some set analysis is included that limits this expression to the dates that actually have associated sales. Here is the final result.
Note we were able to put value labels on the highlighted bars only. Just use the exact same expression, changing the rgb functions to the original expression that drives the chart.
Highlight the Highest and Lowest Values in Qlik Sense
In Qlik Sense, we can use the exact same expression structure. We only have to place it in a different spot. Before we do that, there is a similar effect whereby we can color each bar like a heat map. This might create better analysis depending on your circumstances.
We created a bar chart of sales by Country. The default color scheme is to color each bar the same color. But if we go to edit mode and select Appearance > Colors and legend, swith the toggle from Auto to Custom. Then simply select by By Measure and then enter the same expression that runs the chart. Choose one of the 4 color schemes and you are done.
Now we will build the chart that we came here to create. You can clone the first chart and switch the custom color type from By Measure to By Expression. Use the fx button to open the dialog and use this expression.
Note that in this situation, we do not have the option of limiting the values on the data points to the highlighted bars. It is an all or nothing proposition for now.
This is just a small tip, but one that is complicated enough that a reference like this article is helpful, which is part of the reason why we write these posts 🙂
We tackled some QlikView specific dimension and expression attribute features. Click here to read more: Fine Tuning Your Objects – QlikView Dimension & Expression Attributes
Do you handle this differently? Are there other visualization tips that you want to see? Let us know in the comments.