Group by time interval

The user needs to group and assign the data according to the time interval.

1.1 Expected effect

Example 1: The date before "2008-10-26" is named "cumulative addition", and the time after that is named "greater than", as shown in the figure below:

Example 2: Different time periods are grouped according to "0 o'clock -6 o'clock", "6 o'clock -12 o'clock", "12 o'clock -18 o'clock", "18 o'clock -24 o'clock", and count the numbers according to different intervals, as shown in the figure below shown:

1.2 Implementation ideas

Example 1: Use the IF function for conditional judgment and assignment; use the DATESUBDATE function to calculate the time difference;

Example 2: Use the MID function to extract the time period, use "New Column> Group Assignment" to group the time interval, and perform the sum count within the group.

2.1 Create a self-service dataset

To create a self-service dataset, select certain fields under "Sales DEMO>FRDemo_ORDERS", as shown in the figure below:

2.2 Grouping

Select "Add column", name it "time period grouping", enter the formula: IF(DATESUBDATE (SGINDATE, "2008-10-26", "s")>0, "cumulative addition", "greater than") , click "OK", as shown in the figure below:

Note 1: When adding and subtracting dates, you cannot use "-" directly, you need to use the DATESUBDATE function to calculate the time difference.

Note 2: 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.

Formula description:

Name and save the self-service dataset.

2.3 Effect view

For details, see Example 1 in section 1.1 of this article.

Sample data: Time Interval Grouping.xlsx

Upload sample data to FineBI.

3.1 Create a self-service dataset

Create a self-service dataset and select all the fields under "Time Interval Grouping", as shown in the figure below:

3.2 Take time period

Create a "new column", name it "take time period", enter the formula: MID(Date,12,2), click "OK", as shown in the figure below:

3.3 Perform time interval grouping

Create a "new column", name it "group by time interval", select "group assignment", group the selected fields, and click "OK", as shown in the figure below:

3.4 Count the number after grouping

Create a "new column", name it "1", enter formula 1, count, and click "OK", as shown in the figure below:

Create a "new column", name it "count", select "all values/within the group", set the value rule to sum the counts in the same "group", click "OK", as shown in the figure below:

3.5 Effect view

For details, see Example 2 in section 1.1 of this article.