Group Summary

  • Last update:May 24, 2023
  • Overview

    Version

    FineBI Version
    Functional Change

    6.0

    /

    6.0.3

    Added Group Condition.

    Application Scenarios

    For example, merge data with the same product name and calculate the corresponding remittance amount.

    Functions

    Group Summary refers to merging the same data into one group based on conditions and summarizing and calculating data based on the grouped data.

    You can set group fields and summary fields in FineBI.

    You can find this function in the following two ways:

     1.png

    Explanation of Logic for Week Calculation in Year-Week Granularity

    The week calculation logic of year-week granularity adopts the standard week logic of IOS. The first day of each week is Sunday. There are 52 or 53 weeks each year. The first week of each year must include the day, January 4th. This can ensure that there are 7 days of data every week, which is more in line with business scenarios.

    Example

    This example will show how to use the function Group Summary to calculate the average contract amount of permanent and temporary contracts each quarter.

    Adding Data

    Sample data: 

    Contract.xlsx

    Upload the table from the local and go to data editing interface.

     2.png

    Adding Grouping Fields

    Selecting Grouping Fields

    Click Group Summary to go to the configuration interface.

    Drag and drop the fields Contract Signing Time and Contract Type to grouping and the field Contract Amount to Total.

     3.png

    Set Grouping Way

    Click  > Year Quarter to set the groupring way for the field Contract Signing Time.

     4.png

    Click  > Custom Grouping to set the grouping way for the field Contract Type.

     5.png

    Click Add a group and modify the name as Permanent Business in the pop-up windows.

     6.png

    Select Permanent and click Move to > Permanent Business to move permanent contracts into the Permanent Business group.

     7.png

    Tick off Ungrouped values are assigned to Other in the bottom of the interface and change Other into Temporary Business. Click OK after finishing the settings.

     8.png

    Adding Summary Fields

    Selecting Summary Fields

    Drag and drop the field Contract Amount into Total.

     9.png

    Setting Summary Way

    Click  > Average to set the summary way for the field Contract Amount.

     10.png

    Adding Summary Condition

    FineBI 6.0.3 allows you to add Group Condition for data to be summarized. By using this function, you can filter out data that meets conditions.

    For example, if you want to know details about permanent contracts, you can click  > Group Condition > Add Condition(AND).

     11.png

    Preview

    Click Save and Update in the right upper corner after finishing relevant settings.

     13.png

    Description

    Available Grouping Fields

    text, value, and Date fields are supported. All fields including empty fields can be renamed and deleted.

     

    Field Type

    Supported Grouping Way

    Description

    Text field

    • merge same items as a group

    • Custom Grouping

    • merge same items as a group means to classify/combine text fields of the same value into one group.

    • Custom Grouping means to customize the group for text fields.

    Value field

    • merge same items as a group

    • Interval grouping

    • merge same items as a group means to classify value fields of same values into one group.

    • Interval grouping includes two ways: Auto and Custom Grouping.

    • The interval in Auto is divided into five groups by default according to the maximum and minimum values.

    • Custom Grouping is grouped by default according to the interval set in Auto. The interval can be modified. You can add, modify, delete, and rename customized groups. Those that are still not grouped will automatically be divided into a group with the customized name.

    Date field

    17 grouping ways are supported: Year Month Day, Number of weeks in year, Year-Month, Year Quarter, year, Quarter, Month, Month Day, Weeks, Week, Sun, Hour, Minute, Second, Year Month Day Hour, Year Month Day Hour Minute, and Year Month Day Hour Minute Second.


     

    Available Summary Fields

    text, value, and Date fields are supported. All fields including empty fields can be renamed and deleted.

    Field Type

    Supported Summary Way

    Description

    Text field

    • Distinct Count

    • Records Count

    • String Splicing

    • Note: Only direct connection data is supported.

    • Distinct Count means to count the same values in the field only once. To be specific, it counts the number of different values. It equals to the command count(distinct field).

    • Records Count means to record the number of the field. It equals to the command count(*).

    • String Splicing means to connect values in the field into one value.

    Note: The accuracy of the calculation depends on the type of database and the volume of data. In theory, the smaller the data volume is, the smaller the difference between the deduplication count is.

    Value field

    • Sum

    • Average

    • maximum

    • minimum

    Distinct Count

    Note: Only direct connection data is supported.

    • Records Count

    • Variance

    • standard deviation

    • Median Note: The default option is Sum.

    • Year-on-year/Month-on-month

    You can set year, Quarter, Month, and Week in Year-on-year, Year-on-year growth, and Year-on-year growth rate. For details, see YoY/QoQ/MoM Growth (Quick Calculation).

    Date field

    • Distinct Count

    Note: Only direct connection data is supported.

    • Records Count

    • Earliest Time

    • Latest Time

    • Distinct Count means to count the same values in the field only once. To be specific, it counts the number of different values. It equals to the command count(distinct field)

    Note: The accuracy of the calculation depends on the type of database and the volume of data. In theory, the smaller the data volume is, the smaller the difference between the deduplication count is.

    • Records Count means to record the number of the field. It equals to the command count(*).

    • Earliest Time and Latest Time means the earliest and latest time within groups.


    Notes

    Unavailable Interval Grouping Function for Infinite Values

    When the grouping field is value field and values in the field are infinite, you cannot use the function Interval grouping.

     14.png

    Prompts about Permission Inheritance

    Note: This prompt only appears in extract data.

    When the current user (the creator) is subject to row permission, fields that have been set row permissions cannot be dragged into grouping. The prompt will appear: Due to the permission inheritance, the calculation of the current data is abnormal. You need to add xxx field in xxx table into the group according to the lineage.

    For example, Anna's Region Table is added row permission, which restricts the region as California. When Anna uses this table for grouping and summarizing, she needs to drag and drop the field Region into grouping, otherwise a prompt will appear


    附件列表


    主题: Adding and Editing Data
    Previous
    Next
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy