Number of subsummary statistics

1. Overview

1.1 Expected effect

The total row count counts all occurrences of a dimension field in a certain dimension.

For example, collect statistics on the number of shipmethods in SIGNDATE. As shown in the figure below:

1.2 Application problems

1) Whether FineBI can count directly, which is equivalent to using the COUNT function.

2) Can FineBI implement PivotTable functions in Excel, such as counting function?

3) How to group the same data in a column in the dataset and calculate the number.

1.3 Solutions

1) The number of records (total number of rows) in the dashboard is realized.

2) Use the aggregate function COUNT_AGG in the dashboard.

3) Self-service dataset is implemented by choosing "Group Summary > Records Count".

Take the "ORDERS" in "Package" as an example, count the total number of shipmethods per year.

2.1 Use "Counter" to realize

After creating a new dashboard and component, select "group table". Counter field exists under indicator area on the left. Drag "SIGNDATE", "SHIPMETHOD" and "Counter" fields into dimensions and indicators of the analysis area, as shown in the figure below:

If you need to calculate the total number of shipmethods of each year whose AMOUNT is greater than 30000, you can filter the "Counter", as shown in the figure below:

Note: The schedule not support the function of recording the number.

2.2 Use aggregate function to realize

Use the COUNT_AGG function. COUNT_AGG(array) : Dynamically returns the count of a field based on the current analysis dimension. The result is a dynamic data column. The number of rows is the same as the number of rows in the current analysis dimension.

Note: Array must be the result returned by a non-aggregate function formula. It can be an indicator field, a dimension, or the calculation result between an indicator field and a common formula.

1) Click "+" to add a calculation indicator, select COUNT_AGG function under "aggregation function" on the indicator setting interface, select "SHIPMETHOD" as the field to get the formula COUNT_AGG(SHIPMETHOD), set the indicator name and click "OK", as shown in the figure below:

2) Drag "SIGNDATE", "SHIPMETHOD" and the new "Shipmethod counter" into the dimensions and indicators of the analysis area, then the new "Shipmethod counter" shows the total number of "SHIPMETHOD" per year, as shown in the figure below:

2.3 Use self-service dataset to realize

1) Create a self-service dataset and select the "SIGNDATE" and "SHIPMETHOD" fields in "ORDERS", as shown in the figure below:

2) After selecting fields, click "+" in the left operation process bar to select "Group Summary", as shown in the figure below:

3) Drag the "SIGNDATE" and "SHIPMETHOD" fields into the group column. Group "SIGNDATE" by year and group "SHIPMETHOD" by same value. Drag "SHIPMETHOD" into the summary column, rename it "Shipmethod counter", and select the "Records Counter" from the drop-down list, as shown below:

4) Click "Save" after setting, update the self-service dataset, create a component, drag the "SIGNDATE" and "SHIPMETHOD" into the dimension field, and the "Shipmethod" into the indicator field, which displays the total number of shipmethods per year, as shown in the figure below:

2.4 Preview effect

See section 1.1 of this article for details.