Mark max and min by special colors

1. Overview

1.1 Expected effect

Mark the maximum and minimum values in the table in red and green respectively, as shown in the following figure:

1.2 Implementation ideas

First calculate the size in the self-service dataset, and use the color granularity in the dashboard to display the color.

2. Operation steps

Use data "SALES_Volume" as an example.

2.1 Data preparation

Process the "SALES_Volume" table in the self-service dataset.

1) Add the "sum in group" column, sum the sales volume of each salesperson, and click OK, as shown in the figure below:

2) Add a column "max in group" to count the maximum value of "sum in group" of each salesperson, as shown in the figure below:

3) Add a column "intra group minimum" to count the minimum value of "intra group summation" in each region, as shown in the figure below:

4) Update the "SALES_Volume" to see the self-service data set after setting, as shown in the following figure:

2.2 Set the dashboard

1) Create a new grouping table component in a dashboard, as shown in the following figure:

2) Create a new "flag" field and enter the formula:

IF(SUM_AGG(${Volume})=AVG_AGG(${max in group}),1,IF(SUM_AGG(${Volume})=AVG_AGG(${min in group}),-1,0))

click "OK", as shown in the following figure:

Formula description:

Note: the aggregate function AVG_AGG() must be used here, because the IF function is used to perform secondary calculation on the aggregate function. When the formula contains non aggregate fields, the formula is illegal.

3) Drag the "flag" indicator into the "color" under "table properties" → "indicator", and click the setting button next to "color" to add conditions: green when the indicator is - 1 and red when the indicator is 1, as shown in the following figure:

2.3 View the effect

Click "View all data", and you can see that the maximum value in the component is marked as red and the minimum value is marked as green, as shown in the following figure:

Note: if you do not click "View all data", the marking effect may not be displayed in the dashboard editing interface. You need to enter the preview interface to view the marking effect.