This document explains how to filter data on a dashboard using custom date filters such as: the current week, month or year, the last 7 days. We will leverage the filter tables (link) and show how to link them to a dashboard.

STEP-BY-STEP GUIDE

• Copy the two tables containing the date filters (please follow the procedure described in this document). The FilterTable contains the different filters with the corresponding start and end date.

• Create a parameter to store the user’s filter choice.
  • Click on Object -> Variable
  • Provide a name : in our case Filter type
  • Set its type as String
  • Select Query as Selection List  and bind it to the column type of the FilterTable. This will allow to get the different filters as items of the dropdown list.

• Create a mirror of the FilterTable and filter it using the variable so that we only take the row where the column type is equal to the Filter type parameter.
• Join this table with the one containing your data. If you have a Date field a possible join can be as follows

Date >= StartDateTime and Date <=EndDateTime

Create a dashboard linked to this worksheet

• Create a dashboard linked with the data worksheet you have just created
• Create a combo box to allow the user to select a custom filter
  • Select Query as List Values. Select the FilterTable table, set the label with the column term and value with the column type
  • In the Data tab set the target property to the variable filter type, this will allow to update the parameter value every time the user changes the selection and so update the queried table

• Create some charts based on the filtered table and you should see that data changes accordingly to the filter selection.