Grouping by Time Interval

You need to group and assign data according to time intervals.

Preview

Example one: Dates before 2015/10/26 are named Earlier, and dates after that are named Cumulative New Additions.

Example two: Group by different time periods as 0:00-6:00, 6:00-12:00, 12:00-18:00, 18:00-24:00, and count based on different intervals.

Implementation Method

Example one:

• Use the IF function for conditional assignment.

• Use the DATESUBDATE function to calculate time difference.

Example two:

• Use the MID function to extract time periods, choose Add Column > Group Assignment to group the time intervals, and perform sum and count within each group.

Creating a Subject

Example data: Contract Information.xlsx

Create an analysis subject, and upload the downloaded data table Contract Information (default to selecting all fields).

Grouping

Add the Formula Column step, name it Time Period Grouping, enter the formula: IF(DATESUBDATE(Time of Contract,"2015-10-26","s")>0,"Cumulative New Additions","Earlier"), and click OK.

Note:

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

2. Fields in the formula box need to be selected from the left area and cannot be manually entered.

Formula description:

Click Save And Update.

Effect Display

For details, see section "Preview".

Example data: Time Interval Grouping.xlsx

Creating a Subject

Create a self-service dataset, and upload the downloaded data table Time Interval Grouping (default to selecting all fields).

Extracting the Time Period

Create a Formula Column, name it Extract Time Period, and enter formula: MID(Date,12,2).

After finishing it, you can extract the ''hour'' from the date field.

Grouping by Time Interval

Create an Assignment Column, name it Group by Time Interval, select Group Assignment, group the extracted fields, and click OK.

Counting the Number of Groups

If you want to know the number of times in each time period, you can add a Summary Column called Time Distribution.

Effect Display

For details, see section "Preview".