Concepts → Derived Variable (Preview)

About a derived variable

A derived variable is a dashboard variable whose value is calculated based on the filters and prompts applied to the dashboard. Whenever a dashboard filter or prompt changes the underlying result set, the derived variable is re-evaluated so that its value always reflects the current dashboard context.

A dashboard developer creates a derived variable in the Formula Builder using a formula that must include an aggregate function, such as average or max, returning a single dashboard-level value. The derived variable takes the data type returned by its formula, such as number, string, or date. Typically, a dashboard developer creates a derived variable to centralize a shared calculation, such as an average growth rate, so that filters, conditional formatting rules, and insights across the dashboard can reference a single, consistent value that adapts as users filter the data. For information on managing dashboard filters, refer to Tools → Dashboard Filters Manager.

Components of a derived variable formula

A derived variable formula can be any formula, provided it includes at least one aggregation function that returns a single value. The formula can include the following components:

• Aggregate functions, such as average, max, and sum
• Columns from physical schema tables and business views
• Other built-in functions, such as conditional and arithmetic functions, wrapped around or combined with the aggregation
• Presentation variables, session variables, and global variables

For example, the following formula calculates an average growth rate only when the presentation variable pv1 is set to LY, and otherwise returns 0:

Copy
if(
   $pv1 = 'LY',
   average(
       (sales_comparison.simple_sales_dt.LySales - sales_comparison.simple_sales_dt.Ly2Sales) / sales_comparison.simple_sales_dt.Ly2Sales
   ),
   0
)

Referencing a derived variable in a dashboard

Reference a derived variable using a dollar sign followed by the variable name:

Derived variables are referenced by using a dollar sign ($) followed by the variable name:

$derived_variable_name

For example, if you create a derived variable named dv_avg_gt, reference it as $dv_avg_gt.

A common use case for a derived variable is calculating an average value across a dimension, such as an average growth rate, and highlighting rows that fall above or below it using a conditional formatting rule.

Where derived variables can be referenced

Derived variables can be referenced within an insight in the following locations:

• Individual filter
• Aggregation filter
• Conditional formatting rule
• Insight field

Where derived variables cannot be referenced

Derived variables cannot be referenced in the following locations because the value of a derived variable depends on the filters and prompts applied to the dashboard:

• Runtime filters: the filters that end users apply from the dashboard filter panel
• Dashboard Filters Manager, in the following sections:
  • Prompts
  • Presentation Variables
  • Derived Variables, whether referencing the same variable (self-reference) or another derived variable
  • Applied Filters
  • Filter Options

Example: Highlight states below the average growth rate

The following example uses a derived variable to highlight the states in a sales table whose growth rate falls below the average growth rate of the currently displayed data.

In this example, the derived variable dv_avg_gt calculates the average growth rate as a percentage:

Copy
if(
   $pv1 = 'LY',
   average(
       (sales_comparison.simple_sales_dt.LySales - sales_comparison.simple_sales_dt.Ly2Sales) / sales_comparison.simple_sales_dt.Ly2Sales
   ),
   0
)

An aggregated table insight that displays the State and Growth Rate columns references dv_avg_gt in a conditional formatting rule to highlight rows whose growth rate falls below dv_avg_gt.

When a user filters the dashboard, for example by selecting specific states in the State prompt, the derived variable is re-evaluated against the filtered result set. As a result, the conditional formatting rule automatically updates the highlighting by comparing each row's growth rate with the average of the currently displayed states rather than with a fixed value.

Properties for a derived variable pill

The following are the properties of a derived variable:

Limitations and Known Issues

Consider the following limitations when you work with derived variables:

• Dashboard bookmarks
  Dashboard bookmarks do not store derived variable definitions or values.
• Null values
  Null values are not supported for derived variables and are replaced with their type defaults:
  • "" for string type
  • 0 for numeric and date types
  • 1970-01-01 for date type
  • false for boolean type
• Individual and aggregate analyzer filters
  When filtering a column, presentation and derived variables can only be applied if their data type matches the column's data type. As a result, only variables with a matching data type are available for selection.
  In aggregate filters, columns added by drag and drop can only use variables of double data type, regardless of the column's actual data type. As a result, variables may not be available for some column data types.
  • Workarounds
    Use a formula in the filter to apply the variable, create the presentation variable with the same data type as the column, or cast the value in the derived variable's formula to the needed type.
• Scheduled Excel and CSV exports
  Scheduling dashboards or insights that use derived variables is not supported for Excel and CSV formats. When the schedule runs, the derived variable's value is not displayed in the email body, and an error message is displayed if the insight uses a derived variable in a column or formula.
• Public API query responses
  Querying insights that use derived variables via the Public API endpoint /{tenant}/dashboards/{dashboardGuid}/insights/{insightGuid}/query is not supported and results in errors.