Skip to main content

Building a User Input Control to filter and count by a date range

Updated

Build a Klip using the SWITCH function that counts the number of tickets solved per date and displays the results in a Bar chart. The SWITCH function uses a variable that enables you to select the date range of quarterly, monthly, or weekly from a User Input Control drop-down list.

Connect to the data source you want to build the Klip with. In this example, we are using the columns of data that contain the date and the number of tickets solved from the data source. Build a Klip that contains a User Input Control and a Bar chart that reacts to the selection made in the User Input Control.

To build the User Input Control component and create a variable:

  1. In the Klip Editor, drag the User Input Control component to your workspace.
  2. Enter a Control Label and set the Control Type to Drop-Down list.
  3. Beside the Use Variable option, click the New Variable link.
  4. Create a new variable and set the value to a date range that will be an option in your drop-down list. In this example, a variable called Period is created with a value set to Weekly.
  5. In the Values sub-component of the User Input Control, use the ARRAY function to list the date range options you want in your drop-down list. For example:

ARRAY("This Quarter","This Month","This Week")

To build a Bar Chart that reacts to the User Input Control:

  1. In the Klip Editor, drag a Bar/Line chart component to your workspace. 
  2. In the Series sub-component, enter a Series Label. In this example, the Series Label is Tickets.
  3. With the Series sub-component selected, on the Data tab, select the column of data that contains the number of tickets. For example, @Tickets.
  4. On the X-Axis sub-component, select the column of data that contains the dates. For example, @Date.
  5. Use the Properties panel to choose how to format and display the dates on your Bar chart. You can also set the appearance of each date label.
  6. With Bar/Line Chart selected in your component tree, click Add Hidden Data. 
  7. Select the Data tab and enter a formula that references the variable created when creating the User Input Control and specifies date ranges for each option in your drop-down list. For example:

SWITCH($Period,"This Quarter",DATE_IN(DATE(@Date,"yyyy-MM-dd HH:mm:ss"), quarter), "This Month",DATE_IN(DATE(@Date,"yyyy-MM-dd HH:mm:ss"), month), "This Week",DATE_IN(DATE(@Date,"yyyy-MM-dd HH:mm:ss"), week ))

The SWITCH function switches the variable value ($Period) with the date range, specified by DATE_IN. DATE_IN returns the date values that fall in the specified time period, for example, quarter, month or week. The DATE function is used to convert the format of the date values to Unix time so that the DATE_IN function can process the dates. 

Below is an example of this Klip.

Related articles