All Collections
Data Analyzing
Segmentation of Date variables
Segmentation of Date variables

How to Segment Date Variables in Data Explorer (DeX)

Updated over a week ago

In DataTile, you can easily categorize date variables like "Completion Date", "Date of Birth", and others by breaking them into time series and creating sequential or custom time intervals on the fly.

To create intervals in DeX

  1. Click on the date variable to open the secondary panel, which may be empty or display previously created breaks.

  2. To create new intervals, click on the input field at the top of the panel above the categories.

  3. A hint displaying common expressions will appear. You can either type an expression or choose and edit any suggested in the hint.

  4. Press Enter to apply the expression and generate the specified intervals. Remember that you can edit each interval's label by double-clicking on the created segment individually.

Once intervals are created, you can operate with them just like you would with regular categories.

You can create any number of intervals without restrictions, allowing any duplication or overlapping that might be needed.

To remove existing intervals

  1. Select the intervals

  2. Click the X icon next to them or press the Delete key on your keyboard.

Note! Deleting intervals from the list will not impact existing reports.

Keep in mind that the creation or deletion of intervals does not affect other users or alter the variable. These intervals are exclusive to your account within this specific database and will not interfere with other users or databases.

To create permanent segments for all database users, consider creating a logical variable in Meta-Editor.

Syntax of Segmenting Expression

All the expressions described in this section are case insensitive.

Supported date formats

YYYY/mm/dd

YYYY-mm-dd

YYYY.mm.dd

or the same in the reverse order

dd/mm/YYYY

dd-mm-YYYY

dd.mm.YYYY

Remember that you can simplify the above notations as needed. For example, 2022/01/01, 2022/01, and 2022 are all valid definitions for the same date, but they represent different durations – day, month, and year, respectively.

Single Intervals

Date as it is. You can define a single segment by specifying a period by itself.
For example, 2022/02/24, 2022/02, and 2022 all are valid intervals, designating date, month, and a whole year respectively. In this case, the interval will cover all timings starting from 00:00 till 23:59:59.

Interval defined from a date and its length.

A more advanced approach involves specifying a start date and duration, such as "from 2022/02/24 7d", "from 2022/02/24 1m", or "from 2022/02/24 1q" for one week, one month, and one quarter respectively.

If the duration is a single standard period (d, m, q in this example), you can omit the preceding multiplier. For instance, "from 2022/02/24 m" and "from 2022/02/24 q" are equivalent to the month and quarter definitions above.

Series of Subsequent Intervals

The most verbose notation to define an interval is from dated_period by [N]period*P, where

  • dated_period - is the particular moment in time like it is for single intervals

  • period defines a standard time span which can be d, w, m, q, or y for day, week, month, quarter, or year, respectively (see the table below).

  • [N] is the period multiplier which, together with the period, defines the duration of a single interval.

  • *P is the number of subsequent non-intersecting time intervals to be created.

Used symbol

Interval

d

Day, 24 hours

w

Week, 7 days

m

Month, it’s number of days depends on the calendar

q

Quarter, 3 months

y

Year, 12 months

You can simplify the expression by omitting the "by" keyword and the period multiplier [N] when it equals 1.

Examples of Segmentation Expressions

Example

Explanation

2022/02/24

A single day-long segment

2022/02

A single month-long segment from 1 to 28 of Feb, 2022 (the actual length in days depends on the calendar).

2022

A single year-long segment from Jan 1 to Dec 31, 2022

from 2022/02/24 7d or
from 2022/02/24 1w or
from 2022/02/24 w

One week-long segment.
Equivalent definitions of the same period of one week starting from Feb 24, 2022 inclusively

from 2022/02/24 by 5d*12

12 subsequent segments by five days, first starts from Feb 24, 2022

from 2022/12/31 by 2w*5

5 subsequent non-intersecting bi-weekly segments, starting from Dec 31, 2022

from 2022/11/10 by w*

Subsequent weekly segments, starting from Nov 10, 2022.
The number of segments will be calculated from the raw data to cover all existing periods.

from 2022/01 by m*5

5 subsequent non-intersecting months, starting from Jan, 2022. I.e. {Jan, Feb, Mar, Apr, May}

Did this answer your question?