SQL Calculations

Calculations can be carried out using SQL commands both via the code book and via the text fields (source code). A detailed guide to SQL can be found here:

https://www.w3schools.com/sql/sql_ref_keywords.asp

KPIs such as the Net Promoter Score (NPS score) can be calculated automatically.

Examples:

The following commands are commonly used in DataLion:

Sum: {{=SUM(variable)}}

Sum with condition: {{=SUM(CASE WHEN variable = value THEN sum variable ELSE 0 END)}}

Round: {{=ROUND(variable, decimal places)}}

Count: {{=COUNT(variable)}}

Count distinct values: {{=COUNT(DISTINCT Variable)}}

Division: {{=ROUND(SUM(BranA)/COUNT(BrandA),2)}}

SQL-Calculations vs. SQL Filters

In DataLion it is not only possible to evaluate any formula with SQL calculations, but also filters can be defined with SQL syntax. To distinguish them from SQL formulas that are defined with {{= ... }}, SQL filters are defined with {{ ... }}.

For example, an OR filter can be defined as follows:

{{ (VARIABLE_1 = "Yes" OR VARIABLE_2 = "Yes") }}

Or a filter that counts all characteristics 1, 2, 3 of a variable:

{{ VARIABLE IN (1,2,3) }}

If you visualize a chart that contains an SQL filter, you can display it as absolute values, percentage values, etc. in the normal way.