A parameter is a variable that can be flexibly adjusted. This variable can be used in calculated fields to implement dynamic logic solutions for a single calculated field or even an entire chart. When the parameter takes different values, the field expression will correspond to different calculation logic.

2.1 Set dynamic target values and dynamically calculate target completion rates #

2.1.1 Expected result #

In sales scenarios, target values for a given time period are set as needed on a monthly, weekly, or daily basis. Because the target value changes dynamically based on business needs, the calculated value for target completion rate also changes dynamically. As shown in the figure below, it is possible to manually enter the target value and dynamically calculate the target completion progress.

2.1.2 Implementation plan (public parameters) #

• Click "Data Preparation" in the top navigation bar, then "Dataset". Click "Overview" on the left (by default, you are already on the Overview page, so this step is optional). The "Parameter Management" button will appear on the right.

• Click "Parameter Management" to open the "Parameter Management" dialog box, then click the "Create Parameter" button on the right to start creating a parameter.

• Refer to the figure below and fill in the fields in order:
  • Name -- Custom name
  • Type -- Select Integer here
  • Data option -- Select Manual Input here
  • Initial value -- Any custom value
  • Style -- Default input box
  • Apply dataset -- Select the dataset you intend to use
• Then click OK to save the parameter.
• From the top navigation, select Visual Query, and choose the dataset for which the parameter was set.
• Click the dataset, then click "Add Field" to start configuring the new target completion rate field.

• The numerator of the field expression is obtained from the dataset field, and the dynamically calculated denominator is obtained from the parameter. Double-click these fields to add them to the field expression box, then click the OK button in the lower right corner to save.

• Then, drag the corresponding date, GMV field, and the calculated target completion field into the box as shown below. Add the required filter conditions to obtain the result shown in the figure below. After saving, you can modify the target value to enable dynamic calculations.

2.2 Dynamic comparison of two groups of data #

2.2.1 Implementation result #

There is a dataset where each group number corresponds to a set of data. The requirement is to select two groups, designating one as the experimental group and the other as the control group, to compare their data differences. It should also be possible to switch the experimental and control groups at any time.

The effect achieved using parameters is as follows:

2.2.2 Implementation plan (personal parameters) #

• Click "Data Preparation" in the top navigation bar, then "Dataset". Click "Overview" on the left (by default, you are on the Overview page, so this step is optional). The "Parameter Management" button will appear on the right.

• Click "Parameter Management" to open the "Parameter Management" dialog box. Select "Personal Parameters", then click the "Create Parameter" button on the right to start creating a parameter.

• Add two parameters named "Experimental Group" and "Control Group" respectively. For data options, you can choose list selection or manual input as needed. If you choose list selection, all groups will be displayed for easy selection. If there are too many groups, you can choose manual input.

• Then click OK to save the parameters.
• From the top navigation, select Visual Query and choose the dataset for which parameters have been set.
• Click the dataset, then click "Add Field" to start setting up new metric fields.

• Add new metric fields named "Experimental Group Data" and "Control Group Data". Using the "Data Result" field as an example, the expression for "Experimental Group Data" is: sum(if([组别]={个人}.{实验组},[数据结果],0)), and the expression for "Control Group Data" is: sum(if([组别]={个人}.{对照组},[数据结果],0))
• The "Group" and "Data Result" fields in the field expression are obtained from the dataset fields, while the {Personal}.{Experimental Group} and {Personal}.{Control Group} fields are obtained from parameters. Double-click these fields to add them to the field expression box, then click the Confirm button in the lower right corner to save.

• Next, add these two fields to the chart so that you can dynamically adjust the experimental group and control group data to update the field display results.

• The figure above shows how to use parameters to modify the data for the experimental group and control group. These two data points can then be used to generate other required data. For example, to calculate the percentage difference between the experimental group and control group data, create a new "Difference Percentage" field with the following field expression: ([Personal].[Experimental Group Data]-[Personal].[Control Group Data])/[Personal].[Control Group Data]
• Then, drag the "Difference Percentage" field to the metrics section and set the data format to percentage.
• After that, add the data you want to view to the dimension or metric as needed.
• With richer data dimensions, you can also select more analysis dimensions, switch chart formats, and present more comprehensive analytical results.

3.1 Parameter management #

3.1.1 Public parameters #

Users with project view permission or higher, and at least editing permission for one dataset, can create public parameters.
Public parameters must be configured with applicable datasets; only specified datasets can use these parameters. If there is no editing permission for any dataset, public parameters cannot be added.
When editing the dataset scope for an existing public parameter, regular users can only switch from "All datasets in the project" to "Some datasets" and cannot switch back to "All datasets in the project".

3.1.2 Personal parameters #

Administrators or regular users with project view permission or above can create and manage personal parameters. Personal parameters are at the project level. Personal parameters are available globally within the project, meaning that once personal parameters are created, they can be applied to all datasets.

3.1.3 Parameter format types #

a. Text parameters
Text parameters are divided into manual input, list selection, and dataset value extraction.

Manual input: Users can enter any value for the parameter in the input box for calculation. There are no restrictions on input.
List selection: Options with built-in parameter values. Users can only select from the values in the built-in list.
Dataset value extraction: Values are extracted from datasets, supporting extraction from datasets that are not directly connected to ClickHouse. Supported data sources that are not directly connected to ClickHouse include Hologres, StaRocks, and MySQL.

• In dataset value extraction, it is possible to set field hierarchy, style (single selection/multiple selection), and default value. By clicking Advanced Settings, you can choose to hide null values or empty strings.

b. Integer and floating-point parameters
Integer and floating-point parameters are divided into manual input, list selection, and specified range. Manual input: Users can enter any value for the parameter in the input box for calculation. There are no restrictions on input. List selection: Options with built-in parameter values. Users can only select from the values in the built-in list. Specified range: The range of values users can enter can be limited (maximum and minimum values). By setting the step size, users can be restricted to only use certain values within the range. As shown in the figure below, only the values 1, 3, 5, 7, and 9 can be obtained. Other values are not accessible.

c. Date and time parameters
Date and time parameters only support manual input. The default value of the parameter can be set to a fixed date or a dynamic date.

3.2 Applying parameters #

3.2.1 Dataset - create field #

In the field management section of the dataset editing interface, "Add Field" allows you to use all public parameters applied to the current dataset to create new fields.

3.2.2 Visual query - create field #

In the visual query interface, "Add field" allows you to create new fields using all public parameters and personal parameters applied to the current dataset.

3.3 Support passing filter values from date or metric filters as parameter values #

On the visual query page, if a field in the current chart uses parameters, you can pass the filter value from a date filter or metric filter as the value of the parameter used in the expression. For example, to calculate the average sales amount over a period of time, the calculation is generally sum(sales amount)/{count(days)}. However, if there is a day with no sales amount record, that day will not be counted when calculating the number of days, but in reality, that day should be included. This feature can be used to address this scenario. Steps:

• On the dataset overview page, create two new parameters in the parameter management section in the upper right corner, such as start date and end date, and select "date" as the parameter type.
• Return to the visualization page, create a new field, and set the expression to dateDiff({start date}, {end date}), which calculates the difference between the start date parameter and the end date parameter.

• Select the required date filter, for example, p_date. Click the configuration button in the upper right corner and bind the start date and end date to the corresponding parameters.
• After saving, when you modify the date filter range, the start and end values of the date filter will be passed to the bound parameters.

3.4 Display parameters #

3.4.1 In-chart parameters #

If the field expression used for plotting and analysis contains parameters, or if the field expression directly or indirectly references parameters, then the visual query interface will display this parameter. You can set the visibility of this parameter in "In-chart parameters".

3.4.2 Dashboard global parameters #

In the dashboard's global parameter configuration, you can configure the style and visibility of public and personal parameters.