Trying to Add a Date Dropdown filter that will apply to two different dataset

Question & Answer
, , , , , ,
1

Hi Team, I have Data Set A and Data Set B.

I need two filters on top. “Reporting Month” which will have a dropdown of month in format 2025-01, 2025-02 and “Reporting Date” which will be a date picker. Both should apply to all visuals coming from both datasets.

Data Set A has a date field called Month. I converted the Month filter to string called “Relative Month” and currently have this as a control which has 2025-01, 2025-02, format. Currently this only works for dataset A.

Data Set B has date field called Start_Date and End_Date

I created Reporting Date (date picker) control which is already completed, for this I created two parameters StartDate and EndDate.

Start Date is Start of this month and End Date is Current Date. Both are in DateTime format.

I created two calculated field called Start Date and End Date for it.
Start Date = ifelse(datediff(Start_date, StartDate) <=0 AND datediff(End_Date, EndDate) >0,1,0)

End Date = ifelse(datediff(End_Date, StartDate) <=0 AND datediff(End_Date,EndDate) >0,1,0)

I added this filter and defaulted to 1 and also did the same in dataset A and used Month field there. So this filter (Reporting Date ) is now working for all visuals.

The only problem I am facing now is with Reporting Month filter which only filters to Dataset A. It should basically apply to both the datasets.

I thought I will create another parameter called Reporting Month as a String and list dates like 2025-01, 2025-02 and so on but this will not be dynamic. I need a dynamic date but as a dropdown. I cannot even link this to the dataset and link with “Relative Month” as this is a calculated field coming from dataset A. I think the datetime parameter will also not work.

Is there any way I can make this happen? Just to recap I am trying to make Reporting month filter which is a dropdown apply to the visuals coming from both the datasets which currently just works for dataset A. The filter I am using for this is a calculated field called Relative Month. Thanks.

1 Like
2

Hey @Surs , welcome to the QuickSight community!

I’m not sure I see how you want these two filters to work together. Is your end goal having a filtering system that reduces down the rows in your visuals based on month and then date range?

Could you put this in an arena link?:

3

Hi, you are right. Let me explain you again.

Date Range Filter:

Purpose: Allows users to view data over any selected date range (e.g., from Feb 2024 to Feb 2025). This filter is especially useful for detailed views or historical data where the user wants to see data over a specific period, whether it’s monthly, bi-weekly, or any custom date range.

Reporting Month Filter:

Purpose: Enables users to focus on data from specific months (e.g., January 2025). This filter is particularly useful for monthly KPIs where users need to see performance data for a particular month or set of months. It helps simplify the view by isolating data from the chosen month(s).

Example Scenarios:

  1. Scenario 1:
    o Date Range: Feb 2024 to Feb 2025
    o Reporting Month: January 2025
    o Result: All visuals (monthly KPIs and detailed views) will show data for January 2025 only. The Reporting Month filter takes priority for the KPIs, while the Date Range filter does not affect the monthly KPIs.

  2. Scenario 2:
    o Date Range: Feb 2024 to Feb 2025
    o Reporting Month: Not selected
    o Result: Detailed views (tables) will show data from Feb 2024 to Feb 2025. The Date Range filter applies to all visuals in this case, and no specific month is prioritized.

We want to avoid confusing users with separate filters between visuals, so we’re placing both the Date Range and Reporting Month filters at the top. While users might not use both filters simultaneously, if both are selected, the behavior of all visuals should adjust accordingly for a better, more consistent user experience.

4

Hey @Surs

This makes more sense to me, thank you for the clarification. I would not recommend date filtering this way.

The challenges will be getting your visuals to be able to respond to prioritizing one control parameter over the other. Can you show me how the date fields have been set up in your datasets? Also, is it a requirement for them to be strings rather than truncating the dates if all dates will be measure in months?

Right now my thinking is that you will want to use a calculated field with conditions to decide which date filtering you want to use. You will also need to go from a string date value to a date type. So something like the following:

ifelse(
    ${DateMonthString} = '2025-01' AND extract('MM', {DateField}) = 1,
    ETC...,
    {DateField} >= ${StartDate} AND {DateField} <= ${EndDate},
)
2 Likes
5

Hi, you could create a string parameter “FilterBy” which could be set to “Range” or “Month” and create a visual with List to let a user select one of these values. In this case it is clear what parameter is used, moreover, you can hide the filter that is not used (better user experience, but you should use free form). If you create a calculated field “Filter By” that shows the value of the parameter “FilterBy”, you will be able to use 2 simple shared filters in all applicable datasets, something like this: (Date in [Range] OR “Filter By”!=“Range”) and (MonthOfDate=[Month] OR “Filter By”!=“Month”).

2 Likes
6

These solutions are complicated workarounds on what should be a trivial solution.
Add the option to have a calendar dataset to connect datasets with different date granularity.
Then use filter on the calendar dataset.