How to Dynamically Set Latest Date as Default Parameter and Optimize Refresh Performance in Amazon QuickSight?

Q&A
, , , , , , , ,
1

Hi Community,

I’m facing multiple challenges with a QuickSight pivot table dashboard and would appreciate guidance or best‑practice recommendations.

Background

This dashboard is backed by a dataset that supports two types of refreshes:

  • Daily refresh

  • Weekly refresh

Both daily and weekly data are combined in a single materialized view (MV) at the database level, and the dataset is imported into SPICE.

  • We have a Report Date rank column which is used for Report date filtering in dashboard and below is the values
  • Date with rank 000 -IS the daily data and need to be the default value of filter
    Dates with rank 001 onwards - Weekly history data refreshed on Weekly based.
  • Both are combined in a single dataset and dataset refreshes daily. Underlying Mv also refreshed daily, But the objects configured in the mv for weekly and daily refreshes based on schedule.

image
image334×489 23 KB

1. Dynamically Setting the Latest Date as the Default Parameter

Currently, we need to set the default value of a date parameter to the latest date available in the dataset (i.e., the most recent daily refresh date).

  • The date parameter is of string type

  • The latest daily date changes every day

  • There is no way to dynamically update the default parameter value

  • As a result, we have to manually update the parameter and republish the dashboard daily, which is very time‑consuming

I raised this concern earlier and was advised to submit it as a feature request, but there hasn’t been any enhancement yet.

:backhand_index_pointing_right: Is there any workaround to dynamically set the parameter default value to the latest available date in the dataset?

2. Sorting Date Dropdowns – Rank/Prefix Workaround

Another limitation we are facing is with dropdown sorting:

  • QuickSight dropdown controls always sort values in ascending order

  • There is no native option to sort dates in descending order to show the latest date at the top

To work around this, we introduced a rank/prefix from the database side:

  • 000_YYYY-MM-DD → Daily refresh date (latest)

  • 001_YYYY-MM-DD onward → Weekly refresh dates

This ensures the latest daily date appears first in the dropdown.

However:

  • The parameter default value still has to be manually updated daily

  • The prefixes add complexity and reduce usability

:backhand_index_pointing_right: Is there any way to remove these rank/prefix values and still sort the dropdown in descending order automatically?

3. Dataset Refresh Performance Issues

The dataset:

  • Directly pulls data from the underlying MV

  • Has no calculations in QuickSight

  • Takes ~2 hours to refresh in PROD due to large data volume

Since this refresh runs daily, it significantly impacts usability.

:backhand_index_pointing_right: Questions:

  • Are there QuickSight‑side optimizations to improve dataset refresh performance?

  • Can incremental refresh be used in this scenario?

  • If yes, how can we refresh:

    • Daily data every day

    • Weekly data only during weekly refresh
      when both are combined in a single MV?

Summary

We’ve been facing these issues for a long time, and many of them seem to stem from QuickSight limitations, which negatively impact the user experience.

Any guidance, workaround, or confirmation of limitations would be greatly appreciated.

Thanks in advance for your help!

2

Hi @Ganga,

Nice seeing you back in the Quick Community and thanks for bringing up these concerns again! As for your first two questions, I do not believe this is possible due to your date parameters being string, as it will not be able to tell what the latest date is and therefore rank it based on its typing. I would recommend to rethink the ranking behavior and set up dates with either a calculated field function like parseDate, or rework your dataset so it contains valid dates. If you decide to utilize parseDate, here is some documentation that provides further detail on its capabilities and scope.

In terms of your last question, I do not believe this is possible because the daily and weekly are as you mentioned combined. You would have to separate the daily and weekly in order to implement incremental refresh both ways (and see from there if that improves performance).

Overall, I would recommend the workaround I mentioned regarding the first two questions, which I believe will take care of your default and ranking issues, and should help with improving your refresh performance from there. Let me know if this helps!

3

I have already raised this concern previously as well and based on suggested workarounds only we came up with this approach to add prefixes from db side. other than that whatever workarounds tries from quicksight was not working. And now coming to string data type , we dont have any option in quicksight to display dates as multi value dropdown right ? so based on client requirements and considering too many limitations of quicksight, we have to change it to string to get this in Multivalue dropdown. Everywhere its ‘NOT POSSIBLE’ other than this can we get some response with some alternative approach or ETA for this feature?? Really fed up with all of these Worst side of quicksight.
Could you Please respond with some alternatives for all the 3 questions other than NOT POSSIBLE, LIMITATION, WILL CONSIDER AS FEATURE REQUEST Etc…

4

Hi @Ganga,
I understand your frustration, however please understand that this is a public facing forum, not constantly observed by members of the AWS team; we are simply trying to help if possible so if it is a limitation, than the best option we can provide to assist is tagging it as a feature request. Additionally, as AWS does not disclose information or timetables regarding their current roadmap, we are unable to provide any type of ETA as we are unaware as well.
In regards to your 3 questions:

  1. There is no way to set a dynamic default on a parameter control. Your best option in my opinion is to use a generic ‘Select One’ as your default. I understand that it doesn’t meet your requirement of automatically loading to current day, however it would be one selection to populate the correct information as opposed to you having to repeat your daily process.
    You can have all visuals hidden through a rule as well based on that control, so that your dashboard is not filled with ‘No Data’ messages scattered about.

2. No, there is currently no way to have control over the sorting of a control. I’m not sure how many values you may have at a given time in your dropdown, but another work around I’ve used to get a value to populate at the top is by using a space in front of the value. That will automatically place it at the top of the list while not looking like the dropdown option is indented.
You could test out by adding multiple spaces in front of a word to re-organize, however that could be quite tedious with a large number of value options.

3. In terms of your refresh performance issues. Incremental refresh could be an option in this scenario however could be a bit tricky since your data is coming from one MV. To accomplish this, you would most likely need to add an additional field to your underlying dataset that quantifies if it’s daily or weekly. Then you can setup a rule in your incremental refresh sql for the granularity type to equal daily.
Alternatively, you could separate into 2 separate datasets, daily and weekly so that you can assign different refresh schedules. However this will then create the issue of being unable to build one visual from both datasets.

If you’d like to try and connect directly with AWS support to see if they can assist more directly, you can use the link below: