Filtering by date range

To display records within a specific date range, you can specify start and end dates to filter records. To do this:

  • build two user input control components: start_date and end_date,
  • build a table component to display the related data and
  • create a SELECT statement to connect components.
SELECT( A:A , ( BETWEEN( DATE( C:C , "dd-MMM-yy" ) , DATE( start_date , "dd-MMM-yy" ) , DATE( end_date , "dd-MMM-yy" ) ) ) )


The following data is used in this example. The date format used in the data is dd-MMM-yy and the display date format used in the date pickers is dd-MMM-yyyy. You can use different date formats; see Oracle date formats for details on date formats. NOTE: date picker date formats must include days, for example, dd-MM-yyyy or yy/MM/dd. 

A B C
1 Show Leads Date
2 Ottawa 2 16-May-13
3 Toronto 3 17-May-13
4 Montreal 4 18-May-13
5 Kingston 5 19-May-13
6 Vancouver 3 20-May-13

Step 1: Build the start_date user input control component

  1. Select Build a New Klip > User Input ControlSelect Don't use a data source.
  2. From the component tree, select the Klip component, Klip Title, and type a name for the Klip, for example, Date Picker.
  3. Select the Drop-Down List sub-component, Properties tab, and type a name for the Control Label, for example, start_date.
  4. Set Control Type to Date Picker.
  5. Set Display Format to the date format used in your data source. For this example we use Custom, dd-MMM-yy.
  6. Set Output Format to the date format you want to use for display. For this example we use Custom, dd-MMM-yyyy.
  7. At Set Value for, select Create New Variable, type in a Variable Name and select Create Variable, for example, start_date
  8. Click Save and type a name for your klip. Click Finished.

Step 2: Build the end_date user input control component

  1. Repeat Step 1 to create the end_date user input control component.

Step 3: Build the table component

  1. Select Build a New Klip > Table.
  2. Select Use an existing data source from the library and select your data source.
  3. From the component tree, select the Klip component, Klip Title and type a name for the Klip.
  4. Select Column: Unnamed 1, select the Properties tab and type a Column Header, for example, Show.
  5. Select Column: Unnamed 2, select the Properties tab and type a Column Header, for example, Leads.
  6. Location

    Action

    For our example

    From the component tree, select the first column, Show, and then the Data tab. In the formula editing toolbar, from your data source, select the column from your data source that contains the Show related data. A:A
    A:A selected Select Wrap current value in function > Data Manipulation > SELECT. SELECT( A:A , ( condition ) )
    condition selected From your data source, select the column that contains the date.  
    C:C selected Select Wrap current value in function > Date and Time > Date. SELECT( A:A ) , DATE( C:C , format, timezone )
    format selected Select Insert literal string or number, type the date format from your data source and click Apply. SELECT( A:A ) , DATE( C:C , "dd-MMM-yy", timezone )
    In the formula editing toolbar, select DATE. Select Wrap current value in function > Logic > BETWEEN. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy", timezone ) , start , end )
    start selected Select Insert variable > start_date. SELECT( A:A ) , BETWEEN( DATE( C:C , "dd-MMM-yy", start_date, end )
    start_date selected Select Wrap current value in function > Date and Time > DATE. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy", DATE( start_date, format , timezone ) , end )
    format selected Select Insert literal string or number, type the date format from your data source and click Apply. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy" ), DATE( start_date, dd-MMM-yy, timezone ) , end )
    end selected Select Insert variable > end_date. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy" ) , DATE( start_date, "dd-MMM-yy" ), end_date )
    end_date selected Select Wrap current value in function > Date and Time > DATE. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy" ) , DATE( start_date, "dd-MMM-yy" ) , DATE( end_date , format , timezone ) )
    format selected Select Insert literal string or number, type in the date format from your data source and click Apply. SELECT( A:A ) , ( BETWEEN( DATE( C:C , "dd-MMM-yy" ) , DATE( start_date, "dd-MMM-yy" ) , DATE( end_date , "dd-MMM-yy" , timezone ) )
    In the formula editing toolbar, select the word SELECT. Use Ctrl+C and Ctrl+V to copy and paste the formula to the other columns, changing the first column reference as required. For this example, change to the Leads related data.SELECT( B:B ) , BETWEEN( DATE( C:C , "dd-MMM-yy" ) , DATE( start_date, "dd-MMM-yy" , DATE( end_date, "dd-MMM-yy") ) )
  7. Remove Unnamed Columns and click SaveType a name for your Klip and click Finished.

Step 4: Troubleshooting

  1. Ensure the date formats used in your klip formulas, including any separators, match the date format in your data source.

Feedback and Knowledge Base