Sometimes we'd like to define variables to hold specific metrics and use them throughout the dashboard in several places, with the ability to change / control them in one repository without the need to manually change each formula or widget.
A good use for variables can be setting global targets or setting Min / Max limits for the gauge widget.
For example, we want to set a target of 10% net revenue (which may change in the future),and we’d like to check for each country the gap from that target.
In addition, we want to set the gauge widget barriers using variables in the Min / Max definition. This barrier may change and we don't want to change it manually for each and every gauge.
Steps
The trick of holding the variables is by using an excel sheet which contains the variables in the columns. In order to be able to use this excel we must connect it fictively to the transactions table/s that contain/s the calculated values (to which we want to compare the variables).
Step 1 - Create variables excel
Create an excel sheet containing a ‘Link’ column and the required variables columns. The first column, ‘Link’ will hold the value: 1. The rest of the columns will hold the values of the desired variables.
Import this excel file into the ElastiCube you're working with.
Step 2 - Create link fields in the fact tables
Create “Link” fields in the transactions table/s, if its a regular table use a custom field and define 1 as its value:
if its a custom SQL expression table, add “1 as link” to the final extraction of the fields:
SELECT*, 1 AS Link FROM [Order Details]
Define the “Link” field to be Int type.
Step 3 - Creating a relationship between the tables
Create a relation between the tables and rebuild with schema changes only.
Step 4 - Using the variables
After building the ElastiCube successfully, we now can use these variables with the fact table it is linked to.
we can call a variable using the max() / min() function.
Note:
Do not use the sum() function; it will bring the value according to the relationships it has with the transaction table - we just need to bring it once.
Download:
Variables.dash
Vars.xlsx
Variables.ecdata
