Highlighting Max and Min Values

Application Scenario

When comparing data within the same dimension, you may want to highlight the maximum and minimum values.

For example, you want to highlight the highest and lowest sales volumes of each salesperson, as shown in the following figure.

Implementation Method

You can add Background attributes in Condition Attribute to achieve the effect. Two ways are available for calculating the maximum and minimum values:

1. You can select Summary and Max/Min in Data Setting.

2. You can use the formula MAX() or MIN().

Template Creation

Create a general report, as shown in the following figure.

Data Preparation

Create a database query ds1 with the SQL query statement SELECT * FROM Sales_Volume, as shown in the following figure.

Report Design

1. Merge cells A1 to C1 and enter the title texts, and merge cells A2 and A3, cells B2 and B3 and then enter the header texts. Drag the corresponding data columns into cells A4, B4, C2, C3, and C4, as shown in the following figure.

2. Set Expansion Direction to Vertical for cells C2 and C3, as shown in the following figure.

3. Set Expansion Direction to No Expansion for the cell C4.

Setting the Maximum and Minimum Values

1. Drag the data column Sales_Volume into cells D4 and E4, select Summary and Max in Data Setting for cell D4, and select Summary and Min in Data Setting for cell E4. For example, the following figure shows the steps of setting cell D4.

2. Hide the column D and column E, as shown in the following figure.

Condition Attribute Setting

1. Select cell C3, select Condition Attribute, and add a common condition: Equal to Cell D4, and select Background from the Attribute drop-down list. Select green as the background color for Current Cell, which means that the cell with the maximum sales value will be filled with green, as shown in the following figure.

2. Select cell C3, select Condition Attribute, and add a common condition: Equal to Cell E4, and select Background from the Attribute drop-down list. Select orange as the background color for Current Cell, which means that the cell with the minimum sales value will be filled with orange, as shown in the following figure.

1. Select cell C3, select Condition Attribute, and add a formula condition: C4 = MAX(C4[!0]{B4 = $B4}), which calculates the maximum value expanded from cell C4 associated with the current salesperson. Select Background from the Attribute drop-down list, and select green as the background color for Current Cell, which means that the cell with the maximum sales value will be filled with green, as shown in the following figure.

2. Select cell C3, select Condition Attribute, and add a formula condition: C4 = MIN(C4[!0]{B4 = $B4}), which calculates the maximum value expanded from cell C4 associated with the current salesperson. Select Background from the Attribute drop-down list, and select orange as the background color for Current Cell, which means that the cell with the minimum sales value will be filled with orange, as shown in the following figure.

Method one and method two show the same effect.

1. PC

Save and preview the report, as shown in the following figure.

2. Mobile terminal

The report can be previewed on the DataAnalyst APP and HTML5 terminal, as shown in the following figure.

For details, you can download the templates Highlighting Max and Min Values - Method One.cpt and Highlighting Max and Min Values - Method Two.cpt.