Variable Examples

1 Dataset Date Filtering

To improve dataset query efficiency, it is often necessary to use date filter conditions in datasets, as shown below:

SELECT
       -- ... Omitted SQL
  FROM foodmart.sales_fact_sample a
  JOIN foodmart.time_by_day b ON a.time_id = b.time_id
 WHERE b.the_date >= '${arr.get(dt, 0, cdt.addMonth(-1, "yyyy-MM-dd") )}'
   AND b.the_date <= '${arr.get(dt, 1, cdt.now("yyyy-MM-dd") )}'

Built-in variable function arr.get(array variable, index, default value): Retrieves the value at the specified index of an array variable. Array variables are typically generated by date range query components, but for error tolerance, if the first passed value is a single value, it will still work correctly.

b.the_date <=
   to_date('${arr.get(dt, 1, cdt.now("yyyy-MM-dd") )}', 'yyyy-MM-dd')

1.1 Assigning Date Variables in Self-Service Analysis

Scenario 1: Dataset Variables Not Declared in Advance

We know that variables can be used even if they are not declared in advance. The downside is that if they are not declared in advance, during the self-service analysis (chart design) stage, it is unknown which variables are defined in the dataset, their names, data types, and whether they are arrays or single values.

In this case, we need to manually input variable names (usually variable names need to be uniformly agreed upon or inquired from the dataset maintainer), select variable types, and set variable values. The following operation assigns values to a date range variable:

Scenario 2: Dataset Variables Declared in Advance

If variables and their default values are declared during the dataset maintenance phase, the variable list will automatically pop up when opening variable settings during the chart design stage.

1.2 Assigning Date Variables in Dashboards

Variable assignments in self-service analysis/chart design are temporary and primarily serve self-service analysis, as temporary charts may not be added to dashboards. Additionally, variables in dashboards take precedence over those in self-service analysis. So, how do we assign values to variables during the dashboard design stage?

Steps:

1. Add a date range component.
2. Edit the date range component and associate it with an environment variable (this date control can also be associated with multiple other objects).
3. Set the environment variable key to the variable dt used in the dataset.

2 Binding Variables to Date Dimensions

Through the above method, we have fully implemented dynamic date queries for datasets. However, one inconvenience is that from the self-service analysis stage onward, users must know that date range changes take effect only through variable assignments, which may be a barrier for non-technical users.

If users are unaware of the existence of date variables and only drag date dimensions to the filter bar, the query will combine dataset dynamic date queries and self-service analysis dimension filters, ultimately forming an intersection.

WITH S0 AS (
    SELECT
     -- ... Omitted SQL
    FROM foodmart.sales_fact_sample a
      JOIN foodmart.time_by_day b ON a.time_id = b.time_id
      -- 1 Dataset filter condition: last 10 days
     WHERE b.the_date >= '2025-12-15'
     AND b.the_date <= '2025-12-25'
    )

SELECT DATE_FORMAT(`r_date`, '%Y-%m-%d') AS c_0,
       SUM(`store_sales`) AS v_0
  FROM S0
  -- 2 Dimension filter condition: last year
 WHERE `r_date`>=STR_TO_DATE('2024-12-25', '%Y-%m-%d')
 GROUP BY DATE_FORMAT(`r_date`, '%Y-%m-%d')

Is there a way to use variables for dataset queries while making users unaware of date variables during self-service analysis?

Dashboard Filter Configuration

Similarly, when configuring associations for dashboard filters, users do not need to worry about variables. Simply associate the date filter directly with the date field, allowing dataset users to remain unaware of variables from self-service analysis to dashboard design stages.