To hide " Select All " from a String - based date control

Q&A
, , , , , ,
1

image
image426×439 7.18 KB

image
image419×734 22.8 KB

This Date Created filter is based on the parameter shown above , and the parameter is connected to a calculated field (where the current month is excluded and only previous months are displayed).

Also that calculated field (Date Created - clf ) ignores (returns NULL for) any transactions from the current month and keeps the original date values for all other prior data (all previous months and years) in YYYY/MM format .

I want to hide the “Select All” option from the control. I have tried several methods, but none of them worked. Is it possible to hide the “Select All” option from a string-based date control?

I also want to set the default value to the latest available month (the previous month). It should update automatically whenever the month changes, without requiring any manual effort. is it possible ???

3

Hi @Ardra and welcome to the Quick Community!

I can see from your screenshot that you’ve already checked the “Hide Select all” box. The reason it’s still showing is right in that checkbox’s label: it only takes effect “if the parameter has a default configured.” If your parameter doesn’t have a static default value set, QuickSight ignores the checkbox entirely. Go into your parameter settings (not the control, the parameter itself) and set a static default value. Once that’s saved, “Select all” will disappear from the dropdown.

For defaulting to the previous month automatically, the simplest approach that I have found is to add a column in your dataset query that outputs your normal YYYY/MM values for all months except the previous month or current month, which you label with a static string like “Previous Month” or “Current Month.” Then set that string as your parameter’s static default. Since the underlying data shifts with each dataset refresh, the control always lands on the correct month automatically. This also solves the first issue simultaneously, since you now have a static default configured.

Alternatively, you can set up a Dynamic Default Parameter with a small helper dataset that computes the previous month value via custom SQL. This community thread walks through the pattern. You’d schedule a SPICE refresh on that helper dataset so it stays current. It’s more overhead but useful if you don’t want to modify your source query.

4

Hi @JacobR ,

Thank you for your time and guidance!

I tried implementing the suggested dataset-label approach, but ran into an issue due to how our parameter is configured in the analysis.

Currently, the ${DateCreated} parameter is directly referenced inside a custom calculated field (Date Created Range - Clf), which evaluates date comparisons ({Date Created} <= ${DateCreated}) and filters the visual (Equals 1).

When updating the control to fetch values from the new string label field, the control broke with a red warning exclamation mark (“Refresh this list” error). This happens because the calculated field logic inside the analysis expects ${DateCreated} to match the exact string format/date evaluation rules tied to the underlying date structure, creating a mismatch when swapping out the control values.

Given this setup:

  1. Setting a static default value like 'Previous Month' on the parameter breaks the downstream date comparisons in Date Created Range - Clf.

  2. The parameter control continues to show “Select All” because QuickSight ignores the “Hide [ALL]” checkbox unless a valid static default compatible with the field calculations is set.

Please let me know is there any alternative workaround to hide “Select All” without breaking the downstream string/date calculations, or is keeping “Select All” unavoidable in this case?

image
image267×192 4.06 KB

image
image466×471 22.1 KB

image
image396×120 3.84 KB

5

Hi @Ardra,

The dataset-label approach should work but I think requires adjusting the Date Created Range - Clf to resolve the label back to a real YYYY/MM value before the comparison runs. Here’s what the updated formula looks like:

ifelse(
    isNull({date_created}), 1,
    {date_created} >= truncDate('MM', now()), 0,
    ${DateCreated} = 'ALL' OR isNull(${DateCreated}), 1,
    {Date Created} <= ifelse(${DateCreated} = 'Previous Month', formatDate(addDateTime(-1, 'MM', now()), 'yyyy/MM'), ${DateCreated}), 1,
    0
)

The only change from your existing formula is on line 5. When the parameter holds Previous Month, the ifelse substitutes the actual YYYY/MM value via now(). When the user picks any other month from the dropdown, it passes the string through as-is. Then set Previous Month as the static default on your DateCreated parameter, and the “Hide Select All” checkbox takes effect immediately.

This assumes the label string in your dataset field matches exactly (case-sensitive) what’s set as the static default. So if your dataset outputs Previous Month, the parameter default and the calc field check both need to reference that exact string.

One question: how did you implement the “Previous Month” label on the dataset side when you tried it? Was it a modification to Date Created - Clf directly, or a separate field? The calc field adjustment above needs {Date Created} (the field the control is linked to) to contain that label for the previous month’s rows, so I want to make sure both sides line up.

6

Hi @JacobR

Thank you for the guidance! Here is how I originally had it set up versus how I updated it based on your suggestion:

Previous Setup:

  • Dataset SQL: TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY/MM') AS "Previous Month"

  • Dataset Calculated Field (Date Created - Clf):

    Plaintext

    ifelse({date_created} < truncDate('MM', now()), {Date Created}, NULL)

  • Base Field ( Dataset SQL ) : (Date Created): TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM')

Updated Setup:

  1. Dataset SQL (Date Created):

    SQL

    CASE 
    WHEN TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM') = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY/MM') 
        THEN 'Previous Month'
    ELSE TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM')
END AS "Date Created"

  2. Analysis Calculated Field (Date Created Range - Clf):

    Plaintext

    ifelse(
    isNull({date_created}), 1,
    {date_created} >= truncDate('MM', now()), 0,
    ${DateCreated} = 'ALL' OR isNull(${DateCreated}), 1,
    {Date Created} <= ifelse(
        ${DateCreated} = 'Previous Month' OR ${DateCreated} = 'Previous Months',
        concat(
            toString(extract('YYYY', addDateTime(-1, 'MM', now()))),
            '/',
            ifelse(
                extract('MM', addDateTime(-1, 'MM', now())) < 10,
                concat('0', toString(extract('MM', addDateTime(-1, 'MM', now())))),
                toString(extract('MM', addDateTime(-1, 'MM', now())))
            )
        ),
        ${DateCreated}
    ), 1,
    0
)

  3. Parameter Setting: Set ${DateCreated} static default value to 'Previous Month', and hid the “Select All” option on the control.

Current Status: It is working properly now( like in that attached image) ! The control opens defaulting to 'Previous Month', and “Select All” is successfully hidden.

One question regarding user experience: Is there any way to make the control display the actual month string (e.g., 2026/07) in the dropdown selection box instead of the literal text 'Previous Month', while still retaining the dynamic monthly roll without manual parameter updates? Or is displaying the static alias 'Previous Month' the expected trade-off in QuickSight to make this dynamic default pattern work?

Thanks again for all your help!

image
image552×982 39.3 KB

7

Hi @Ardra - this is the expected tradeoff. You can set the month string of “Previous Month” on the database side, the calculation side, but not as a dynamic default for the parameter.

As a workaround - you might add a note somewhere in the report explaining what “Previous Month” is showing the user (as an on-page context setter, not in the control). You’d create a calculation returning the previous month dataset-wide, and render it inside a crosstab to show the value.

What it would look like if this feature becomes available in the future:

SQL:

CASE 
    WHEN TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM') = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY/MM') 
        THEN 'Previous Month' || ' ' || TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM') 
    ELSE TO_CHAR(VoucherRedeem.date_created, 'YYYY/MM')
END AS "Date Created"

Analysis Calculated Field:

ifelse(
    isNull({date_created}), 1,
    {date_created} >= truncDate('MM', now()), 0,
    ${DateCreated} = 'ALL' OR isNull(${DateCreated}), 1,
    {Date Created} <= ifelse(
        ${DateCreated} = 'Previous Month' OR ${DateCreated} = concat('Previous Months', ' ', [LOGIC TO CREATE PREVIOUS MONTH STRING AS YYYY/MM],
        concat(
            toString(extract('YYYY', addDateTime(-1, 'MM', now()))),
            '/',
            ifelse(
                extract('MM', addDateTime(-1, 'MM', now())) < 10,
                concat('0', toString(extract('MM', addDateTime(-1, 'MM', now())))),
                toString(extract('MM', addDateTime(-1, 'MM', now())))
            )
        ),
        ${DateCreated}
    ), 1,
    0
)

Let me know if this helps!

Good luck,

Dave