I have a date filter with an OR condition (using 2 date fields from dataset) and only want 1 calendar control on the sheet to update both filters. Is that possible? The only way I can get the filters to work is to have 2 calendar controls n the sheet which isn’t very user friendly.
Thanks @Biswajit_1993 I have a dataset with 60 columns of data and multiple date fields. Create date, closed date, answer date, resolution date. I’ve added 40 columns to a quicksight sheet including 4 date columns. I need to provide users a way to filter the data based on 2 date fields. If the closed date OR the create date is between the date range users select those records should show. I only want 1 date range filter to show on the sheet so users can select their date range. When I created a group filter using both of those date fields only the first field of the filter updates, the second date field does not update so the records are not displaying correctly. I’ve only gotten this to work by creating separate date filters for each column. Having users update 2 date filters isn’t user friendly. How can 1 date filter update 2 date columns?
Thanx @Carlos_Ranel for explaining in details I am trying my end by using some custom data once I will find the solutions I will get back to you.
Hello @Carlos_Ranel, Thank you for posting your query. Based on my understanding of your problem, I have tried a solution approach. Please find the details below.
Step 1 : Created a Sample Dataset having the following columns where I have 3 different date fields Order Date, Ship Date and Delivery Date.
Step 2 : Create 2 parameters and attach a Date Picker control to both of them so that users can select the Start Date and End Date respectively using the controls.
Step 3 : Create a Calculated Field (e.g. Matching Criteria) with a logic similar to the below based on your use case. Here I am trying to see the products whose Order Date or Ship Date belong to the range selected by the user using the Calendar Controls. You can change it according to your need, but the concept remains same
ifelse(({order_date} >= ${paramStartDate} AND {order_date} <= ${paramEndDate}) OR ({ship_date} >= ${paramStartDate} AND {order_date} <= ${paramEndDate}), 1, 0)
Step 4 : Mark this new Calculated Field as Dimension and bring it to your Table Visual and ensure that you hide the column in your visual.
Step 5 : Create a filter on the Visual with the criteria the the new Calculated Filed (i.e. Matching Criteria) is always equal to 1.
This should solve your use case. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
2 Likes
@sagmukhe thanks for the post. I made one tweak to the matching expression. I assume it was just a typo but in the last line you have ship date in the first part and order date in the second part, I put ship date in both parts like below:
{ship_date} >= ${paramStartDate} AND {ship_date} <= ${paramEndDate}), 1, 0)
Following all other steps this works as I need it to work and is a good solution.
1 Like
Hi @sagmukhe , Good to learn new concepts in QuickSight so that when use cases will come to us we can take reference from this.
I think @Carlos_Ranel got the solutions.
Thanks to both of you.
1 Like
Love this post @Biswajit_1993. @sagmukhe Thanks for all your amazing solutions. @Carlos_Ranel Glad you found the solution you were looking for. Happy week all! 