Displaying records within a 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 using date and time formats for details. 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.

Step 4: Troubleshooting

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