Common User Input Control scenarios: Counting all tickets by quarter, by month, by weekday

Some formulas are used more often than others when building Klips. In this series of articles, a Klip is built using a common set of formulas to give a better understanding of how these formulas work within a sample data set.

This Klip counts the date for all tickets and displays the results as a quarterly, monthly, or weekday date range.

The SWITCH function uses a variable that allows the user to select a date range of either quarter, month or weekday from a drop-down list. For more information on the SWITCH function, see SWITCH. For more information about using variables, see User Input Control component.

This Klip uses the DATE_UNITVALUE function which is responsible for retrieving dates for the time periods: quarter, month, or days of the week.

To build this Klip, you will:

  • Build a User Input Control component
  • Create a variable
  • Build a Bar/Line Chart
  • Create GROUP and GROUPBY statements using the SWITCH function in the X and Series sub-components.

This Klip uses the following data source:

Step 1: Building a User Input Control component

  1. Select Build a New Klip.
  2. Select the User Input Control component.
  3. Select Don't use a data source option.

Goal: Create a variable to use in Bar/Line chart formulas.

  1. From the component tree, select Klip, and type a Klip Title.
  2. From the component tree, select Drop-Down List.
  3. At the Properties panel, provide the required information:

Field

Required information

For our example

Control Label Enter a name for the drop down list. Period selection
Create New Variable Enter the name for the variable to be used in the Bar/Line chart formulas. kperiod
  1. From the component tree, select Values.
  2. From the formula editing toolbar, select Insert function, ARRAY.
  3. With data selected, click the Insert literal string or number button and type quarter.
  4. Repeat Step 9 for month and weekday selections.
  5. Save this Klip.

Step 2: Building the X-Axis of a Bar/Line Chart component

Goal: Use the GROUP function to return a unique instance of values for a given time period.

Note: If the column has a row header in your data source, wrap each column reference in the SLICE function. By default, the SLICE function removes the column or row header. For more information, see SLICE function. This data source has a row header. If your data source does not, you can skip this step in the instructions.

  1. Select Build a New Klip.
  2. Select the Bar/Line Chart component.
  3. Select Use an existing data source from the library and select your data source.
  4. From the component tree, select Klip, and type a Klip Title.

Step 3: Building the Series of the Bar/Line Chart component

Goal: Use the GROUPBY function to count the date entries for a given time period.