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.

Location

Action

For our example

From the component tree select Column: Unnamed 1.  
In the formula editing toolbar

select Insert function, Data Manipulation, SELECT.
The SELECT function returns data using start and end dates selected by the user.

SELECT( data , ( condition ) )
With data selected select the column that contains the date - Column A. SELECT( A:A , ( condition ) )
With A:A selected select Wrap current value in function, Data Manipulation, SLICE. SELECT( SLICE( A:A , start , end ) ,   ( condition ) )  
With condition selected select Insert function, Logic, BETWEEN. SELECT( SLICE( A:A ) , ( BETWEEN( values , start , end )
With values selected select the column that contains the date - Column A. SELECT( SLICE ( A:A ) , ( BETWEEN( A:A , start , end )
With the second A:A selected select Wrap current value in function, Date and Time, DATE.
This converts the date to a number - epoch time - so that a date calculation can be performed.
SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , format , timezone ) start , end )) ) 
With format selected select Insert literal string or number, type the format of the date that appears in your data source, and click the Apply button. SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy", timezone ) start , end )
With start selected

select Insert variable and select the start date variable you created earlier.
This allows the user to select a start date.

SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy ) fruitstartdate, end )))
With fruitstartdate selected Select Wrap current value in function, Date and Time, DATE.
This converts the date to a number - epoch time - so that a date calculation can be performed.
SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate, format , timezone ) , end )))
With format selected select Insert literal string or number and type the format of the date in your data source, and click the Apply button. SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate , "d-MMM-yy", timezone ) , end )
With end selected

select Insert variable and select the end date variable you created earlier.
This allows the user to select a start date.

SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate , "d-MMM-yy" ) , fruitenddate ))) 
With fruitenddate selected Select Wrap current value in function, Date and Time, DATE.
This converts the date to a number - epoch time - so that a date calculation can be performed.
SELECT( SLICE( A:A , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate , "d-MMM-yy" ) , DATE ( fruitenddate , format , timezone ))
With format selected select Insert literal string or number and type the format of the date in your data source, and click the Apply button. SELECT( SLICE( A:A ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate, "d-MMM-yy" ) , DATE ( fruitenddate , "d-MMM-yy" , timezone ))
With SELECT selected in the formula editing toolbar using Ctrl+C and Ctrl+V, copy and paste the formula into Column: Unnamed 2 sub-component and change the first column reference to Column B. SELECT( SLICE( B:B ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate, "d-MMM-yy" ) , DATE ( fruitenddate , "d-MMM-yy" ))))
using Ctrl+V, paste the formula in a new Column: Unnamed 3 sub-component and change the first column reference to Column C. SELECT( SLICE( C:C ) , ( BETWEEN( DATE( A:A ) , "d-MMM-yy" ) , DATE( fruitstartdate, "d-MMM-yy" ) , DATE ( fruitenddate , "d-MMM-yy" ))))
Repeat for column D of the data source. SELECT( SLICE( D:D ) , ( BETWEEN( DATE( A:A , "d-MMM-yy" ) , DATE( fruitstartdate, "d-MMM-yy" ) , DATE ( fruitenddate , "d-MMM-yy" ))))
Select the Properties panel for each column. Type a Column Header and select the following types for Format as. Date - Text ,
Country - Text ,
Fruit Type - Text,
Boxes - Number

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.

Klip Tutorials

  1. Introduction
  2. Introduction to the Build-a-Klip Tutorial
  3. Build-a-Klip Tutorial: Phase One
  4. Build-a-Klip Tutorial: Phase Two
  5. Build-a-Klip Tutorial: Phase Three
  6. Build-a-Klip Tutorial: Phase Four
  7. Build-a-Klip Tutorial: Phase Five
  8. Example - Revenue YTD Klip
  9. Example - Financial Performance YTD Klip
  10. Example - World News Klip
  11. Example - Bookings by Country Klip
  12. Example - Bookings QTD Klip
  13. Example - Stock Quotes Klip
  14. Example - Marketing Traffic Sources Klip
  15. Example - 75/20 Service Level - Today Klip
  16. Training Klip - Array, Slice, Reverse, If, and Trim Functions
  17. Training Klip - Sum, Average, and Count Functions
  18. Training Klip - Group and Groupby Functions
  19. Training Klip - Date, Datevalue, and Today Functions
  20. Training Klip - Replace function and image URLs
  21. Training Klip - Concat and Join Functions
  22. Common Bar/Line Chart scenarios: Example of using the LOOKUP function with a condition to align data
  23. Common Bar/Line Chart scenarios: Example of using the LOOKUP function to align data
  24. Common User Input Control scenarios: Displaying total boxes of fruit sold in the last three months
  25. Common User Input Control scenarios: Counting all tickets by quarter, by month, by weekday
  26. Common Table scenarios: Selecting start and end dates to display number of boxes sold by country
  27. Common Table scenarios: Sorting data to display the video with the highest number of views
  28. Common Pie Chart scenarios: Displaying total number of surveys as a percentage by country
  29. Common Pie Chart scenarios: Displaying numbers and percentages in a pie chart legend

Feedback and Knowledge Base