SQL calculations

SQL commands let you run calculations both from the codebook and from the text fields (source code). You can find a detailed SQL reference 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 frequently used in DataLion:

Sum:   {{=SUM(Variable)}}

Sum with condition:   {{=SUM(CASE WHEN Variable = Value THEN SumVariable ELSE 0 END)}}

Round:  {{=ROUND(Variable, Decimals)}}

Count:  {{=COUNT(Variable)}}

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

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

Percentage share of the values = “1 or 2” among the values = “1 or 2 or 3 or 4” of the variable var_item:    {{= (SUM(CASE WHEN var_item IN (1,2) THEN 1 ELSE NULL END)/SUM(CASE WHEN var_item IN (1,2,3,4) THEN 1 ELSE NULL END))*100}}

Mean: {{= AVG(var_item)}}

Mean of defined values of a variable: {{= (SUM(CASE WHEN var_item IN (1,2,3,4) THEN var_item ELSE NULL END)/COUNT(CASE WHEN var_item IN (1,2,3,4) THEN 1 ELSE NULL END))}}

SQL calculations vs. SQL filters

In DataLion you can not only evaluate arbitrary formulas with SQL calculations, you can also define filters using SQL syntax. To distinguish them from SQL formulas, which are defined with {{= … }}, SQL filters are defined with {{ … }}. 

An OR filter, for example, can be defined as follows:

{{ (VARIABLE_1 = “Yes” OR VARIABLE_2 = “Yes”) }}

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

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

When you visualize a chart that contains an SQL filter, you can display it as absolute values, percentages and so on, just as usual.