Common Table scenarios: Selecting start and end dates to display number of boxes sold by country

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 allows you to select a start date and end date to display the number of boxes of apples or oranges sold in either Canada or New Zealand.

This Klip lets the user drill down by date, country, and fruit type. For more information about using drilldown, see Using drill down in a Table component.

This Klip uses the SELECT function with the BETWEEN function. The BETWEEN function contains the start and end date variable which are created in a date picker User Input Control component.

To build this Klip, you will:

  • Build two User Input Control components
  • Create start and end date "date picker" variables
  • Build a Table component
  • Create SELECT statements that use the BETWEEN function
  • Configure the table for drilldown

This Klip uses the following data source:

Step 1: Building a User Input Control component

Note: If the column has a row header in your data source, you normally 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.

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

Goal: Create start and end date variables for use in Table 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, do the following:

Field

Required information

For our example

Control Type From the drop down list, select Date Picker.  
Display Format Select Custom and enter the date format in your data source. d-MMM-yy
Output Format Select Custom and enter the date format in your data source. d-MMM-yy
Control Label Enter a message to tell the user to select a start date. Choose a start date
Create New Variable Enter a name for the start date variable. fruitstartdate
  1. Save this Klip.
  2. Repeat Steps 1-8 to build the fruitenddate variable.

Step 2: Building columns in the Table component

Goal: Use the SELECT function to create date, country, fruit type and box column formulas.

  1. Select Build a New Klip.
  2. Select the Table 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 2: Enabling drill down for the Table component

Goal: To apply grouping to date, country, and fruit types columns and aggregate (sum) the number of boxes.

  1. Select the Table component.
  2. Select the Drilldown tab.
  3. Select Enable Drilldown and complete the following tasks.

Field

Required information

 1 For Group by select Date and click the Add Drill Level button.
 2 For Group by select Country and click the Add Drill Level button.
 3 For Group by select Fruit.
3 Click the Configure other columns at this level link. For Boxes, ensure Sum is selected, and click the Finished button.
  Save the Klip.