Transformations

Transformations are query parameters that transform value expressions in a SQL query, e.g. selected data for the purpose of aggregation.

Date/Time Aggregation

Date/Time aggregation parameters are transformations that allow writing queries that aggregate selected data at a particular granularity of a timestamp, with the granularity either fixed or captured by a UI control. To let the granularity be defined by a UI control, the aggregation transformation can be used with the following general syntax:

{<datetime_expr>:aggregation}

When used, the query is automatically associated with the following UI control on any dashboard showing the report that uses this query:

Date/Time aggregation transformations make use of DATE_TRUNC or analogous SQL functions, depending on the type of data source. For example, the following parameterized query

SELECT {saletime:aggregation}, SUM(revenue)
FROM sales
GROUP BY {saletime:aggregation}
ORDER BY {saletime:aggregation}

produces the following (slightly simplified) SQL on a Redshift data source:

SELECT DATE_TRUNC('day', saletime), SUM(revenue)
FROM sales
GROUP BY DATE_TRUNC('day', saletime)
ORDER BY DATE_TRUNC('day', saletime)

As can be seen in this example, date/time aggregation transformations are typically used in conjunction with grouping the results by the aggregated timestamps and applying an aggregate function, hence the name of the transformation.

There are also fixed date/time aggregations which are not associated with a UI control. The following example always displays revenue by week:

SELECT {saletime:week}, SUM(revenue)
FROM sales
GROUP BY {saletime:week}
ORDER BY {saletime:week}

The supported fixed time aggregations are listed in the table below.

Basic Parameters

The are a few additional basic parameters available in queries when the aggregation transformation is used:

• {aggregation.value}: Replaced by the currently selected aggregation period, e.g. "day", as a quoted string.
• {aggregation.value_raw}: Replaced by the currently selected aggregation period, e.g. day, as is in the SQL.