Recoding and Calculations

How can I recode or calculate variables?

You can also create new variables in the code book by using existing variables in the data set for new values or a calculation (command language: SQL).

You can find a collection of commands here.

Recoding:

Recode means to create a new variable by formulating conditions based on an existing variable. Recodes can be used as filters.

Calculation means that a new variable is calculated from an existing variable. Calculated variables cannot be used as filters.

Basis for recodes: In order for the recodes to be used as filters, they must always be formulated with the following brackets {{( before the condition and )}} after the condition.

Examples of recodes:

Example 1: Creation of Top2 boxes

The condition of the Top2 box is that the values 1 and 2 of variable f5, in this case the Top2 of the variable, are added together.

Formula: {{(f5='1' OR f5='2')}}

Example 2: formation of age groups

In the present case, age was recorded numerically in variable f6. In order to form age groups, conditions such as 'all respondents who are 18 and older and younger than 36' are formulated.

Formula: {{(f6>= 18 AND f6<= 35)}}

Example 3: Definition of User/Non-user

The variables f1, f2 and f3 have 6 values.

If a respondent has selected for at least one of the three variables (OR) value 1, 2 or 3(AND), he is considered a user.

Formula User (to be entered in the value field):

 {{( (f1>=1 AND f1<=3) OR (f2>=1 AND f2<=3) OR (f3>=1 AND f3<=3) )}}

Consequently, those respondents who only selected values 4, 5 or 6(AND) for all variables f1, f2 and f3(AND) or did not answer the questions at all (value: -99(OR)) are considered non-users.

Formula Non-user (to be entered in the value field):

{{( (f1>=4 AND f1<=6 OR f1=-99) AND (f2>=6 AND f2<=6 OR f2=-99) AND (f3>=4 AND f3<=6 OR f3=-99) )}}

Example 4: Count if

If a variable contains genre labels such as Drama, the frequency of the Drama genre can be counted using the following formula (to be entered in the value field):

{{(genre LIKE '%Drama%')}}

Calculations

Basis for calculations:

• Do not put spaces before/after calculation instructions such as SUM
• Since calculated variables cannot be used as filters, the brackets {{ before and }} after the calculation statement are sufficient
• If you specify the chart type b-bar, the curly brackets must be followed by an equals sign {{=
• If you specify the chart type calculation, the equals sign is not required {{

Example 1: Calculation of NPS score

Example 2: Calculation of a total variable

With the following formula (to be entered in the value field) you can calculate a total variable, i.e. a variable in which all cases occurring in the data set are counted:

{{= COUNT(*)}}

Example 3: Percentage on a different basis

With the following formula (to be entered in the value field), the value 1 of a variable f1 can be expressed as a percentage on a different basis, here on the basis of all values that are total value 1 in the variable:

{{=(100*(SUM(f1 IN (1)))/(SUM(total IN (1))))}}

Example 4: Mean without calculating missing values

If a variable f1 contains missing values (defined as e.g. -77), the calculation of the mean takes the -77 into account and therefore a nonsensical value is returned. The mean value of variable f1 is calculated without the missing values using the following formula (to be entered in the value field):

{{=SUM(CASE WHEN f1= "-77" THEN f1 ELSE 0 END) / SUM(f1= "-77")}}

Example 5: Calculation of the absolute change in the turnover (12M, last 12 months)

With the following formula (to be entered in the value field) you can calculate the absolute change in the turnover:

SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_10) THEN turnover ELSE 0 end) - SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 24 month) FROM dashboard_data_table_10) AND period < (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_10) THEN turnover ELSE 0 end)

Example 6: Calculation of the change in the turnover in % (12M, last 12 months)

With the following formula (enter in the value field) you can calculate the change in the turnover as a percentage:

100 * (SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_10)