Relative Date Expressions

available since 16.0

QueryAPI supports relative date expressions in Groovy API. Instead of hard-coding absolute dates into your queries or dashboard filters, you can build conditions that are evaluated at runtime, such as “today”, “now”, or “today ± N days”.

Example 1 – Last 7 Days of Sales

This example shows how to use a “today” literal and relative subtraction (−7 days) in a QueryAPI filter. This can enable a “Last 7 days” dashboard filter without hard‑coding dates.

import java.time.Duration

def qapi  = api.queryApi()
def exprs = qapi.exprs()
def sales = qapi.tables().datamart("Standard_Sales_Data")

def today       = exprs.dateOnlyToday()
def sevenDaysAgo = today.minus(Duration.ofDays(7))

return qapi.source(
        sales,
        [sales["InvoiceDate"], sales["InvoicePrice"]],
        exprs.and(
                sales["InvoiceDate"].greaterOrEqual(sevenDaysAgo),
                sales["InvoiceDate"].lessOrEqual(today)
        )
).stream { it.toList() }

Example 2 – Next 30 Days Validity Window

This example shows how to define a rolling 30‑day horizon (e.g., for “offers valid in the next 30 days”).

import java.time.Duration

def qapi  = api.queryApi()
def exprs = qapi.exprs()
def cr    = qapi.tables().conditionRecords()

def today      = exprs.dateOnlyToday()
def in30Days   = today.plus(Duration.ofDays(30))

return qapi.source(
        cr,
        [cr.key1(), cr.validFrom(), cr.validTo()],
        exprs.and(
                cr.validFrom().lessOrEqual(today),
                cr.validTo().greaterOrEqual(in30Days)
        )
).stream { it.toList() }

Example 3 – Dashboard “Custom” Filter Using Relative Dates

This example shows how Query Definitions can plug relative date expressions into a single filter (dateFilter) behind the “Custom” date option. Different UI choices (e.g., “Last 7 days”, “Last 30 days”, “Next month”) can map to different combinations of relative start/end expressions in Groovy.

import java.time.Duration

def qapi  = api.queryApi()
def exprs = qapi.exprs()
def sales = qapi.tables().datamart("Standard_Sales_Data")

// Example: “From 30 days ago until today” as a reusable building block
def today        = exprs.dateOnlyToday()
def thirtyDaysAgo = today.minus(Duration.ofDays(30))

Expression dateFilter = exprs.and(
        sales.getAt("InvoiceDate").greaterOrEqual(thirtyDaysAgo),
        sales.getAt("InvoiceDate").lessOrEqual(today)
)

return qapi.source(
        sales,
        [sales["CustomerId"], sales["InvoiceDate"], sales["InvoicePrice"]],
        dateFilter
).stream { it.toList() }