Variables
Description
Variables can be used to store content, which can be re-used in different places of your analysis.
There are two types of variables:
Text replacements: These variables store plain text and can be used for example to set title or text within a component, including text inside PQL statements.
Static values: These variables contain a PQL statement which will be executed independently of where the variable is used. This allows you to calculate statements independent of the dimensions used in a component. Use this type of variables for example to calculate a ratio.
Both types of variables can be used in different places of an analysis, for example in component titles or component attributes like dimension names, as well as inside PQL queries.
Variables cannot be used inside other variables, however you can use them inside saved formulas.
Syntax
Both types of variables can be accessed inside an analysis in appropriate places with
<%= variable_name %>
Text replacements
Text replacement variables can be used to replace characters inside a PQL query before the query is evaluated.
Example
As an example, imagine you have two columns in your activity table containing the activity strings: ACTIVITY_EN contains the activity strings in English, ACTIVITY_DE contains the same information in German. To make it easy for the user to switch between both languages in the Analysis, you could create a variable called LANGUAGE, with content EN or DE. In the PQL queries, use the following snippet to reference the activity column based on the current variable content:
"ACTIVITIES"."ACTIVITY_<%=LANGUAGE%>"
This will now reference the activity column for the language key specified in the variable (the variable placeholder is replaced with the actual string value of the variable). If the variable value is changed (for example by the user using a text input component), the column referenced in the PQL statement changes accordingly.
Static values
Static variables contain PQL statements which will be executed independently of where you use it. Analysis FILTERs will be respected, but Sheet- and Component Filters as well as user selections are not taken into account. The result of the query is stored inside the variable and this value can then be used inside another PQL query or in other places inside an Analysis, for example in a component title. If the PQL statement returns more than one row, only the value in the first row will be written to the variable. Therefore, usually an aggregation function is used inside the query to only get one single value. In the analysis and especially inside other PQL queries, such a variable behaves like if you would write the resulting value directly into the analysis, rather than executing the underlying PQL query.
Example
As an example, imagine you have an INT column named URGENCY, where the value marks how urgent it is to handle an incident. Now you want to compare each urgency value with the overall average urgency value.
You cannot write this:
CASE WHEN "INCIDENT"."URGENCY" > AVG("INCIDENT"."URGENCY") THEN 'Urgent' ELSE 'Less urgent' END
This does not work since you cannot use aggregations and dimensions inside the same function or operator.
However you can solve this very easily by calculating the average with the following statement inside a variable called AVG_URGENCY:
AVG("INCIDENT"."URGENCY")
You can then use this variable inside the CASE WHEN condition:
CASE WHEN "INCIDENT"."URGENCY" > <%=AVG_URGENCY%> THEN 'Urgent' ELSE 'Less urgent' END