Maximum available Date in Date Pick

Question & Answer
,
1

Hello all,

I’m new to the tool, trying to implement really simple Date filter, I have a Date filter which checks for the records in between the two date parameters(say from and to date). I provided my Default to date as 9999/12/31. When I the run report it shows all the records, but when i try to pick a todate say 2023/07/09 and want to go back to 9999/12/31 I’m not able to go back and maximum date in date picker is 2099/12/31. The only possible way i found so far was to reset the control which would take me back to my default setting. Please let me know if there is any other way ?

Thankyou!

2

Hi @harika11, welcome to the QuickSight Community. The behavior you described is expected when using date filters. The date picker’s maximum date limit is typically set to 2099/12/31, and there isn’t a direct way to revert to a default value like 9999/12/31 without resetting the control.

Here are a few options you might consider:

  1. Reset Control: As you mentioned, resetting the control is currently the most straightforward way to revert to the default value. This action would return the date filter to its initial state, including the default to date of 9999/12/31.
  2. Custom Date Range: Instead of relying on the date picker, you could provide users with a custom date range control. This control could include options such as “All Dates” or “9999/12/31” to allow users to easily revert to the default state.
  3. User Education: You could provide guidance to users on how to effectively use the date picker, including the limitations of the maximum date and the option to reset the control if they need to return to the default setting.

While these options might not provide the exact functionality you’re seeking, they can help users effectively manage date filters in Amazon QuickSight. Additionally, it’s always worth checking for updates and new features in QuickSight, as improvements to date filtering functionality could be introduced in future releases. So please keep an eye on the What’s New / Blog . You can set up a Watching Alert by clicking on the bell icon.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

3

Thankyou so much for the quick response, I would like to give Custom Date control a try, Could you please suggest how do i implement this?

4

Hi @harika11, implementing a custom date control in Amazon QuickSight to handle a special default value like “9999/12/31” involves a bit of creative solutioning since QuickSight’s native date picker doesn’t support such a far future date directly.

Here’s how you can approach creating a custom date range control:

Step 1: Use Parameters to Create Custom Date Inputs

  1. Create Parameters: Go to your QuickSight dashboard and start by creating two parameters:
  • CustomFromDate: This will hold the starting date of your range.
  • CustomToDate: This will hold the ending date of your range.
  1. Set Default Values: For CustomFromDate, you might set a realistic minimum date like 2000/01/01. For CustomToDate, set the default value as today’s date or any date that suits your dataset.

Step 2: Add Controls to Dashboard

Add these parameters as controls to your dashboard:

  • Go to the dashboard’s “Analysis” mode.
  • Under the “Add” menu, select “Add control”.
  • Choose “Date” as the type of control for each parameter.
  • Link each control to its respective parameter (CustomFromDate and CustomToDate).

Step 3: Implement a Custom Option for “All Dates”

Since the maximum date that can be selected from QuickSight’s date picker is restricted to 2099/12/31, you can implement a workaround by using a calculated field or an additional parameter:

  1. Create an Additional Parameter: Let’s call it AllDatesIndicator.
  • This could be a string parameter with options such as “All Dates” and “Specify Range”.
  1. Add this as a Control: Add this parameter as a drop-down control on your dashboard.

Step 4: Use Calculated Fields to Apply Filters

Create a calculated field that will dynamically filter data based on the selected date range or the “All Dates” option:

FilteredData = ifelse({AllDatesIndicator} = 'All Dates', 1, 
                      ifelse({date_field} >= {CustomFromDate} and {date_field} <= {CustomToDate}, 1, 0))

Here, {date_field} should be replaced with the actual date field from your dataset.

Step 5: Apply the Calculated Field as a Filter

  • Apply the FilteredData calculated field as a filter on your visuals, and set it to equal 1. This filter will now control whether the visuals display all records or just those within the specified date range.

Step 6: Guide Users on How to Use the Custom Control

Provide clear instructions on your dashboard on how users can select “All Dates” or specify a custom range using the controls provided.

Conclusion

This approach allows you to bypass the limitations of the native date picker by using parameters and a calculated field as a custom filtering mechanism. It provides flexibility and ensures that users can easily switch between viewing all data or just a subset based on the date range, including an effective way to handle extreme date values like “9999/12/31”.

5

Thankyou for the detailed explanation, this helps a lot!