Dynamic Display of the top N

 1.Overview

1.1 Expected effect

After ranking the sales, you can filter out the stores with the top 30% of sales. That is, when the number of stores is 20, the top six are filtered out, as shown below:

The example uses Demo data "store sales statistics".

Use this data sheet to create components.

2.1 Add calculation indicators

2.1.1 Ranking sales

Enter the component edit page, add the calculation field "rank", and rank the "AMOUNT" in descending order. Use the formula RANK_ANLS, RANK_ANLS(SUM_AGG(AMOUNT),0,"desc"), click "OK", as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

2.1.2 Find the number of stores that dynamically change

Add a new calculation field "total_customers". Use the formula TOTAL to calculate the maximum value of the rank to find out how many customers are there in total. Enter the formula: TOTAL(rank, 0, "max") and click "OK", as shown in the figure below:

2.2 Making a table component

1) Drag "CUSTOMERID" into the dimension column, "AMOUNT", "rank" and "total_customer" into the indicator column, and select the icon type as the group table, as shown in the figure below:

2) Click the "rank" drop-down to cancel the second calculation. This operation is for the subsequent filtering operation, the system will not re-rank the filtered results. As shown below:

3) Select "drop-down>filter" for the "CUSTOMERID" field, as shown in the figure below:

2.3 Filter out the top 30% of stores

Set filter conditions: rank(Aggregation)<=total_customer(Aggregation)*0.3, as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

There are a total of 21 stores, and the top 30% are the top 6 stores in terms of sales. The filtered effect is shown in the figure below:

2.4 Add text filtering component

Enter the dashboard editing interface and select "Filter component>Text drop-down", as shown in the figure below:

Select "CUSTOMERID" under "FRDemo_ORDERS" into the field column, as shown in the figure below:

The text drop-down filter 10 stores, the ranking will display the top 3, as shown in the figure below:

2.5 Effect view

For details, please refer to section 1.1 of this article.

3. Notice

3.1 Filter out the top 50% of stores

The ROUND function returns the nearest integer after calculation. The formula is: rank(Aggregation)<=ROUND(total_customer(Aggregation)*0.5,0), and the filter conditions are set as what is shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

The preview area automatically filters out the top 50% "CUSTOMERID" of "AMOUNT", and the filtered effect is shown in the figure below:

3.2 Filter out the top three stores

We set filter conditions, and the formula is: rank <= 3, as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

The filtered effect is shown in the figure below: