Overview
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.
Example One
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:
| Formula | Description |
DATESUBDATE(Time of Contract,"2015-10-26","s") | Return the time difference between Time of Contract and 2015/10/26 in seconds. Time of Contract — 2015/10/26 |
IF(DATESUBDATE(Time of Contract, "2015-10-26", "s") > 0,"Cumulative New Additions","Earlier") | If the time difference is greater than 0, assign the new column as Cumulative New Additions, otherwise assign it as Earlier. |
Click Save And Update.
Effect Display
For details, see section "Preview".
Example Two
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".
