Many times you will want to present different measures for the same time period, but these measures are filtered by different date fields.
This article will demonstrate how to create one simple date filter in the dashboard that is connected to as many date fields as needed, allowing the user to select one time frame and view the measures without hassle.
Some cases where you might want to filter multiple date fields include:
If you want to allow your users to filter multiple date fields, you know they will filter the same time period in all of the filters but you have no choice but to give them multiple date filters to use.
In cases where there are different date fields in the same fact table you have to either:
Split the table to multiple fact tables and connect them to a main DimDates table
Pivot the data to contain fields of ‘date’ and ‘date type’, creating huge duplications of data, messy modeling, and many different date filters in the same dashboard.
The above solutions, although helpful, are hard to perform and maintain, especially if you already have an existing cube in production and you’re worried about changing it and about the performance and stability of the whole solution.
Another case this solution may help you with is if you have the same date field in different timezone adjustments - you might want to filter the different timezone fields to the same date range to catch all data in this range.
Prerequisites:
Install Sisense Filtered Measures
In the following example we will treat three date fields, two of them in the same fact table, and the third one in a different table. The cube does not contain a DimDates table.
We want to create a widget that will show for each country:
Number of Approved Orders
Number of Delivered Orders
Number of Reviews
Those measures should all need to be reflected by the same date range selection, but there are three different date fields to filter.
Steps:
Create a new Date Range Filter widget with the required fields:
2. Add the fields as dashboard filters and rename them for easier usage:
3. Create the pivot and add dimensions. Add the formula and insert filtered measure tag to connect the measure to the required date field, and add ALL() for the other date fields you don’t want to filter the specific measure:
( [# of unique Order ID],[@OrderApproved],
all([Days in order_estimated_delivery_date]),
all([Days in review_creation_date]) )
4. Create the two other formulas (and as many others as you need) and hit apply
And here is the final Dashboard View:
You can also create widgets with measures that only relate to one of the date fields and turn off the other date fields for the whole widget:
