Counting the Number of Identical Values by Group and Summary

Expected Effect

You can use the Count function to count a dimension field by another specific dimension.

For example, you can count each brand (with products sold) by the dimension Sales Date, as shown in the following figure.

Application Question

1. Can data be directly counted in FineBI, similar to the use of the COUNT function?

2. Can FineBI support functions (for example, the count function) similar to the pivot table function of Excel?

3. How to group the same data in a specific column of a dataset and then count the data?

Implementation Method

1. You can use the Count function in the component to achieve the expected effect.

2. You can use the aggregation function COUNTD_AGG in the component.

3. You can choose Group Summary > Summary and select Count from the drop-down list of the selected field to achieve the expected effect in datasets.

Taking the Store_Sales_Statistics table in the Retail Industry business package as an example, you can obtain the total count of each brand with products sold every day.

Sample data: Store Sales Data Statistics.xlsx

Implementation by Count

1. After creating a component, select Group Table in Chart Type. In this case, the Count field exists in the to-be-analyzed area on the left. Drag the sale date and Brand description fields into the Dimension bar and the Count field into the Indicator bar in the analysis area, respectively, as shown in the following figure.

2. To calculate the total count of each brand with more than 200 products sold every day, add a filter condition for the Count field, as shown in the following figure.

Note:

You cannot use the Count function in detail tables.

Implementation by Aggregation Function

You can use the COUNT_AGG function. COUNTD_AGG(Array): According to the current analysis dimension, this function returns the count of a certain field dynamically. The generated results are listed in a dynamic data column. The number of rows in the data column is consistent with that in the current analysis dimension.

Note:

The array needs to be any non-aggregation expression.

1. Click the + icon in the to-be-analyzed area, click Add Calculation Field, choose Function > Aggregation Function > COUNT_AGG, and select the Brand description field on the indicator setting page. In this case, the formula COUNT_AGG(Brand description) is obtained. Name the indicator and click OK, as shown in the following figure.

2. Drag the sale date and Brand description fields into the Dimension bar and the obtained Daily Brand Count field into the Indicator bar, respectively. The obtained Daily Brand Count field displays the total count of each brand with products sold every day, as shown in the following figure.

Implementation by Dataset

1. Create an analysis subject, and select the sale date and Brand description fields from the Store_Sales_Statistics table, as shown in the following figure.

2. After selecting the fields, select Group Summary and drag the sale date and Brand description fields into the Group bar. In this case, the values of sale date are in the format of Year-Month-Day, and identical values of Brand description are grouped together. Drag the Brand description field into the Summary bar, rename the field Daily Brand Count, and select Count from the drop-down list, as shown in the following figure.

3. After completing the above setting, click Update and Save to update the dataset. Create a component, drag the sale date and Brand description fields into the Dimension bar, and drag the Daily Brand Count into the Indicator bar. The Daily Brand Count field displays the total count of each brand with products sold every day, as shown in the following figure.

Effect Display

For details, see section "Expected Effect."