Dropdown menu for queries with dropdown-placeholders

The "Placeholder Formulas" feature allows you to flexibly compare data depending on different values of another variable, for example for different time periods. Depending on the dropdown selection, the SQL formula is filled with target variables. In this way, different values of the dropdown variable can be output in different charts on a dashboard.

A variable for the dropdown menu is created in the codebook, for example "month". Their values have a consecutive number across the rows.

In the "Settings" column in the codebook, {"use_as_filter": false} is entered for the drop-down question. This prevents the dropdown value from being both set as a placeholder and filtered on. This also means that this question generally cannot be used as a filter. Please note that variables that do not contain a placeholder for this drop-down question are unaffected by the selection from this drop-down menu.

The formula of the variable that should react to the dropdown menu contains a placeholder: %%Row_ID_DropdownQuestion%%. At this placeholder, the system later uses the Row_ID of the value selected in the dropdown menu, e.g. for April (Row_ID 17). A "-1" is added to the placeholder in a formula that is to output the value for the previous instance, for example. This means that the formula later contains the Row_ID of the value selected in the dropdown minus one, i.e. in this case the Row_ID of the previous month (March Row_ID 16).

In our example, the value (here: KPI) is calculated with a CASE WHEN condition depending on the selected value or the selected value-1 of the dropdown filter.
KPImon = {{CASE WHEN month=%%13%% THEN KPI ELSE NULL END}}
KPImon-1= {{CASE WHEN month=%%13%%-1 THEN KPI ELSE NULL END}}