hypanis.ru Qlikview Visual Cues - Highlight Maximum and Minimum Aggregate Values - BuffaloBI.com

# 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:

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:

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:

• 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!