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
,
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