We need the rundate parameter in an Amazon QuickSight dashboard to have a dynamic default value based on the current day. If the dashboard is accessed on a Monday, it should automatically load data for the previous Friday; otherwise, it should default to

Q&A
,
1

Has anyone implemented the following requirement in an Amazon QuickSight dashboard?

We have a dataset that uses a direct query with a rundate parameter, for example:

SQL sample code using the direct query :

SELECT *
FROM table_name
WHERE rundate = <rundate_parameter>

We need the rundate parameter to have a dynamic default value based on the current day:

If today is Monday, the default rundate should be Friday’s date.
For all other days, the default rundate should be yesterday’s date.

Additionally, users should be able to manually change the rundate parameter in the dashboard, overriding the default logic when needed.

Has anyone achieved this in QuickSight? If so, what approach did you use for setting the dynamic default value while still allowing user overrides?

2

Hi @nnaik06,

Welcome to the Quick Community! I have not tried this for a specific use case, but this might be achievable by editing your SQL in a way to add another field (that accounts as your default rundate) like this:

SELECT
  CASE
    WHEN EXTRACT(DOW FROM CURRENT_DATE) = 1        -- Monday
      THEN CURRENT_DATE - INTERVAL '3 day'         -- Friday
    ELSE CURRENT_DATE - INTERVAL '1 day'           -- yesterday
  END AS default_rundate

From there, for the parameter dynamic default you should be able to define “User name column” to whatever value best makes sense for your use case, and then setting “Column for default value” to that newly created field.

Let me know if something like helps or if I misunderstood your use case at all!

3

Hi @nnaik06 ,

I’d take a look at my response here: Control Filter with Rolling Default by date? - #6 by Dave_LeBlanc

@WLS-Luis 's reply should provide the correct framework for deriving the default run date in the dataset layer, but to set the dynamic default (as I understand it) you will also need a column containing the ARN of the user (or group), and giving everyone a blanket default value of the SQL-derived date.

They should still be able to change the parameter to whatever value needed, the above should provide the mechanism for setting it.

Let me know if this helps!

Dave

4

Hi @nnaik06

Here’s another approach: keep your direct-query dataset as-is and move the date rule into its Custom SQL, driven by two QuickSight dataset parameters.

QuickSight’s dynamic defaults are designed for per-user/group lookups from a mapping table — they can’t evaluate a calendar rule at query time, and a calculated field can’t serve as a dynamic default. When the default is one shared business rule for all readers (not a different date per person), the logic belongs in the database query itself.

Dataset Parameters

Parameter Type Values Analysis Default
RunDateMode String, single value DEFAULT, MANUAL DEFAULT
RunDateOverride Date, single value Any date Today (ignored unless mode = MANUAL)

Custom SQL (Redshift Direct Query)

WITH runtime AS (
    SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', GETDATE())::DATE
        AS business_today
)
SELECT
    t.record_id, t.rundate, t.region, t.category,
    t.order_count, t.revenue_usd
FROM your_table AS t
CROSS JOIN runtime AS r
WHERE t.rundate =
    CASE
        WHEN <<$RunDateMode>> = 'MANUAL'
            THEN (<<$RunDateOverride>>)::DATE
        WHEN DATE_PART('dow', r.business_today) = 1   -- Monday
            THEN DATEADD(day, -3, r.business_today)::DATE
        ELSE DATEADD(day, -1, r.business_today)::DATE
    END

Controls (in the Analysis)

  • Map both dataset parameters to analysis parameters.
  • RunDateMode → Dropdown with DEFAULT / MANUAL only (hide [SELECT ALL]).
  • RunDateOverride → Date picker, YYYY-MM-DD. Always visible; the SQL only reads it when mode = MANUAL.

Behavior

Today is Rundate defaults to
Monday Prior Friday (−3)
Tuesday–Sunday Yesterday (−1)
MANUAL mode The date you pick

Tested in Redshift: DATE_PART('dow') returns 0 = Sunday … 6 = Saturday, so the = 1 branch catches Monday → prior Friday, and the ELSE branch gives yesterday for every other day. MANUAL passes the selected date straight through.

Timezone Note

GETDATE() / CURRENT_DATE honor the session TIMEZONE. The example above assumes the session is UTC, which is why we convert to America/New_York. If your Redshift session timezone is already America/New_York, simplify to:

SELECT CURRENT_DATE AS business_today

Using GETDATE() without conversion in that case would double-shift and produce the wrong date.

Optional Extension: Weekends → Friday

If you later want Saturday and Sunday to also resolve to the prior Friday (common for weekly reporting), extend the CASE:

WHEN DATE_PART('dow', r.business_today) = 0   -- Sunday  → Friday
    THEN DATEADD(day, -2, r.business_today)::DATE
WHEN DATE_PART('dow', r.business_today) = 1   -- Monday  → Friday
    THEN DATEADD(day, -3, r.business_today)::DATE
WHEN DATE_PART('dow', r.business_today) = 6   -- Saturday → Friday
    THEN DATEADD(day, -1, r.business_today)::DATE
ELSE DATEADD(day, -1, r.business_today)::DATE
5

Hi @nnaik06,

Just checking back in since this thread hasn’t received a response in a while. Was Dave, EZV and I’s replies helpful to you and/or were you able to find a solution yourself in the meantime? Please help the community by marking this answer as “Solution” or following up in general within the next 3 business days!

Thank you!

6

Hi @nnaik06,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you!