Qlikview Visual Cues – Highlight Maximum and Minimum Aggregate Values

The following post will demonstrate how to use Qlikview Visual Cues in a Straight Table chart, to highlight the maximum and minimum values for each expression over a given dimension.  In this example, I will setup the chart to calculate Average Point totals per year, for two teams.  I use Set Analysis to isolate each team in a separate column, then use Visual Cues to set the background color of the maximum and minimum averages per team (green is maximum, red is minimum).

This post covers several advanced functionalities, but the main purpose is to demonstrate how to use Visual Cues for a specific purpose.

Qlikview Visual Cues – Highlight Maximum and Minimum Aggregate Values

For this example, I created the following inline table script that you can use for sample data:

Next, once the data is loaded, add a Straight Table object to your Qlikview application.

Use the following settings:

Caution:  When you copy and paste the expressions below, you may need to reformat the apostrophe!!

  • Dimension:  Year
  • Setup three Expressions
    • Expression 1:  =Avg(Points)
      • Label:  Average
    • Expression 2:  =Avg({$<Team={‘Buffalo’}>}Points)
      • Label:  Buffalo
    • Expression 3:  =Avg({$<Team={‘Pittsburgh’}>}Points)
      • Label:  Pittsburgh

 

Apply these settings to the chart, and you should have the following:

Qlikview Visual Cues - Highlight Maximum and Minimum Aggregate Values

Next, setup the following Visual Cues:

Caution:  When you copy and paste the expressions below, you may need to reformat the apostrophe!!

  • Expression “Average”
    • Upper:  Max(Aggr(Avg(Points), Year))
      • Set Text to White
      • Set Background to Green
      • Check the box for Bold
    • Lower:  Min(Aggr(Avg(Points), Year))
      • Set Text to White
      • Set Background to Red
      • Check the box for Bold
  • Expression “Buffalo”
    • Upper: Max(Aggr(Avg({$<Team={‘Buffalo’}>}Points), Year))
      • Set Text to White
      • Set Background to Green
      • Check the box for Bold
    • Lower: Min(Aggr(Avg({$<Team={‘Buffalo’}>}Points), Year))
      • Set Text to White
      • Set Background to Red
      • Check the box for Bold
  • Expression “Pittsburgh”
    • Upper: Max(Aggr(Avg({$<Team={‘Pittsburgh’}>}Points), Year))
      • Set Text to White
      • Set Background to Green
      • Check the box for Bold
    • Lower: Min(Aggr(Avg({$<Team={‘Pittsburgh’}>}Points), Year))
      • Set Text to White
      • Set Background to Red
      • Check the box for Bold

Apply these settings and you will have the following:

Qlikview Visual Cues - Highlight Maximum and Minimum Aggregate Values

Now your chart will:

  • Highlight the maximum average in green for each dimension value (Average, Buffalo and Pittsburgh)
  • Highlight the minimum aggregate average in red for each dimension value (Average, Buffalo and Pittsburgh)

I hope you find this post useful!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment