Show TOC

Function documentationRelative Date Filtering Locate this document in the navigation structure

 

You can enter specific values to define relative filters for dates. These can be used for time-dependent comparisons. For example, you would like to compare new documents created on the current day.

To perform relative date filtering, that is, calculate relative dates instead of absolute dates, cross-data comparison provides a syntax for defining fixed points and offsets for relative dates.

Prerequisites

In the Details popup for a mapped field or a source-specific field, you have set the filter type to Relative Timestamp.

Features

  • Instead of a fixed (absolute) date, you can enter a keyword for the start date (using $ as a prefix) and optionally an additional offset for the difference in days:

    Syntax = <StartDate>[<Difference>]

    You can replace <StartDate> with the following keywords:

    Keyword for <StartDate>

    Description

    Example using 2013/04/25 as a reference date

    $TODAY

    current date (today)

    2013/04/25

    $FDOCW

    first day of current week

    2013/04/22

    $LDOCW

    last day of current week

    2013/04/28

    $FDOCM

    first day of current month

    2013/04/01

    $LDOCM

    last day of current month

    2013/04/30

    $FDOCY

    first day of current year

    2013/01/01

    $LDOCY

    last day of current year

    2013/12/31

    $FDOPW

    first day of previous week

    2013/04/15

    $LDOPW

    last day of previous week

    2013/04/21

    $FDOPM

    first day of previous month

    2013/03/01

    $LDOPM

    last day of previous month

    2013/03/31

    $FDOPY

    first day of previous year

    2012/01/01

    $LDOPY

    last day of previous year

    2012/12/31

    $FDONW

    first day of next week

    2013/04/29

    $LDONW

    last day of next week

    2013/05/05

    $FDONM

    first day of next month

    2013/05/01

    $LDONM

    last day of next month

    2013/05/31

    $FDONY

    first day of next year

    2014/01/01

    $LDONY

    last day of next year

    2014/12/31

    $TIMES

    time stamp now (offset in seconds)

    n/a

    $TIMEM

    time stamp now (offset in minutes)

    n/a

    $TIMEH

    time stamp now (offset in hours)

    n/a

    $TIMED

    time stamp now (offset in days)

    n/a

    $DELTA

    delta mode (timestamp of last run)

    n/a

  • Optionally, you can enter the <difference> as a positive or negative offset in days, using the following signs:

    • + increments days, that is, the start date is moved into the future

    • - decrements days, that is, the start date is moved into the past

    Example Example

    $TODAY-2: day before yesterday

    $TODAY+2: day after tomorrow

    End of the example.

  • You can combine the relative date selection in ranges, using the From and To fields of the selection criteria, for example, to define intervals.

    Example Example

    To count the amount of documents with a creation date in the last month, you enter the following selection criteria: <Field Name> = $FDOPM to $LDOPM.

    End of the example.

  • For the ABAP source type, filter fields of the following data types support the syntax for relative dates:

    DDic Data Type

    ABAP Data Type

    Date Format

    DATS

    D

    YYYYMMDD

    CHAR 10

    C 10

    YYYYMMDD

    CHAR 14

    C 14

    YYYYMMDDhhmmss

    DEC 15

    P 8

    YYYYMMDDhhmmss

    DEC 21

    P 11

    YYYYMMDDhhmmss,mmmuuun

    TIMS

    C 6

    hhmmss

    Legend:

    • YYYY = year

    • MM = month

    • DD = day

    • hh = hour

    • mm = minute

    • ss = second

    • mmmuuun = milliseconds / microseconds / nanoseconds

    Note Note

    In data types containing time stamps (hhmmss), by default, 000000 is entered by the system.

    Exception: For start date keywords using a “Last Day” option (for example, $LDOCM) a maximum time stamp (235959) is set by the system.

    The keywords $TIMEx and $DELTA are filled with exact values.

    End of the note.

  • You can also use relative date filtering for the ADBC source type. However, the supported date and time stamp data types depend on the used database management system.

  • For the $TIMEx and $DELTA keywords, the system calculates an exact time, and not only a date.

    The following keywords use the current system time stamp at the time of data comparison: $TIMES, $TIMEM, $TIMEH, $TIMED. The system calculates exact time stamps using DEC 15 and DEC 21 data types (see table above). You can use different time units for the offset, for example, seconds, minutes, hours, or days.

    $DELTA stores the time stamp of each data comparison run, and at the next run, it selects the data created since the last run, by entering >=$DELTA in the time stamp filter. You can enter an additional offset in minutes (+-<Difference>).

    Both keywords support an additional <Difference> offset. For $DELTA , the entered value is interpreted as minutes.

    The time stamp calculation is based on UTC time. If the used database table stores the time stamps in another time format, you can use the <Difference> operator to adjust the value.