The Codebook - Part 2

The Codebook is a metafile that defines a project's default settings and links the data set to DataLion.

This allows data to be visualized easily and quickly. The default settings can also be flexibly changed via the admin area of the dashboard.

Default settings:

• Navigation structure (several variables can be bundled, for example, thematically, which provides the clarity)
• Summary of variables in a chart
• Captions (e.g. question text and values)
• Chart type selection

If new code books are frequently created, it makes sense to always work with the same arrangement of the columns.

In Part 1 you will find an overview of all columns.

How can multiple answers be displayed in one chart?

In the codebook, multiple responses (variables with "0/1" coding) can be bundled into one chart. This works via the "Question ID" column. Variables containing the same question ID are bundled into one chart.

Example (before):

Example (bundled):

How can stacked bar charts be displayed in one chart?

Multiple stacked column charts can also be displayed in one chart in the codebook. The chart type "multistack" is used for this and each characteristic is created one after the other. The assignment to a chart works via the "Question ID" column. Characteristics that contain the same question ID are bundled into one chart.

Example:

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: https://www.sibilla-egen-schule.de/schule/sch-service/anleit/Befehlssammlung_SQL.pdf

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 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 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 to be 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 equal 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 value 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) 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)) / SUM(CASE WHEN period > (SELECT DATE_SUB(MAX(period), INTERVAL 12 month) FROM dashboard_data_table_10) THEN turnover ELSE 0 end)

How can I read in non-numeric variables?

You can also create "labels" in the code book, if the variables are not stored numerically.

Example: Data set with "Labels"

Special columns - sorting of values

The order of the values can also be specified in the code book.

If you do not define the order, DataLion arranges the values one after the other according to the row ID. Sorting can be specified using the "numeric" column.

"Numeric" Column

1,2,3,4,...: Sorting consecutively according to the specified numbering.

In the chart settings you can change the sorting order (e.g. sort in ascending or descending order). If you want to specify a value in the sorting, e.g. always put "Other" at the end, this is possible through an additional column.

"Sort Order" Column:

1: does not sort a value or always puts it in the last place

0: all other values that should remain sortable

Example 1: "Numeric" column

Example 2: "Sort Order" Column

When uploading the codebook, the new columns must be linked to the "Numeric" or "Sorting Order" column.

Special Columns – configuration of the navigation menu

The navigation menu can also be specified in the codebook, i.e. which variables should be shown and hidden (on the question/measure and filter level).

"Position_id" column

Special columns - color scheme

In the codebook, an individual color scheme can also be configured for each question/measure.

"Settings" column