Highlighting the Highest and Lowest Values in Your Charts

Featured Image - LivingQlik Tips - A sign featuring the low and high roads

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?

The Powerful QlikView Minichart - Built in highlighting

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.

Normal QlikView Chart

The standard bar chart in QlikView before our adjustments.

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

Script to highlight top and bottom values

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.

QlikView bar chart with colored min and max

The finished QlikView chart.

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.

Script excerpt to label the top and bottom points.

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.

Heat Map Bar Chart in Qlik Sense

This is an easily created “heat map version of the chart.

 

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.

Qlik Sense Chart Highlighting the top and bottom values.

The finished chart in Qlik Sense.

script-to-highlight-top-and-bottom-value-qlik-sense

 

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.

Final Thoughts

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.

 

5 comments… add one
  • Reply Denis Brennan December 7, 2016, 4:51 pm

    The Sense expression is missing!

  • Reply Aaron Couron December 7, 2016, 5:11 pm

    You are absolutely right Denis!!!! Sorry about the oversight. I have added it. Thanks for letting me know.

  • Reply Martin Dideriksen December 13, 2016, 9:29 am

    When I only have to highlight the min and max-values, I usually do something like this.

    If(Rank(Column(1))=1,Green(),
    If(Rank(-Column(1))=1,Red())
    )

  • Reply Aaron Couron December 13, 2016, 1:33 pm

    Martin,
    My experience with the rank function has not been pleasant as it does not deal well with null, zero values and negative values at times.
    That is not really a sufficient answer, and when I have time, will try to add the rank function to the article and/or explain why they should or should not be used.
    Food for thought. Thanks for the comment!

    • Reply Martin Dideriksen December 16, 2016, 8:42 am

      I have not yet seen any issues when using the rank-function referring to a column in the chart (such as Column(1)). But yes, i have seen issues with the rank function when putting a sum inside the rank.
      As always – what works in one solution or the next ten QS/QV-applications, does not necessarily work in the next one 🙂

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons