Hey all,
I have a requirement to add only the Month and Year portion of a date as a dropdown in the dashboard. Using a date parameter doesn’t allow drop down option, which is why I’ve had to convert the field to string and then apply it. Here’s the pic
Now, the issue is by default it picks ‘Select all’, that’s because I’m not able to assign a default value. Quicksight currently only allows Static default values.
My requirement is it needs to update to maximum/latest date i.e. dynamic default depending on when the data is refreshed.
Please suggest
Hi @Ops_Expert,
You can set a dynamic default.
You can create a string parameter, link it to your filter and set a dynamic default. QuickSight allows you to set a different dynamic default for each reader or group but you can just set the same default for all readers using a calculated field like this:
Dataset Refresh Date =
concat(extract(‘YYYY’, now()), ‘/’, extract(‘DD’, now()))
1 Like
Thanks for the response. The other issue is- dynamic default value can only be added as a column from the table. So, I’m trying to add a new column calculated as max date from date field.
Also, max(string) is not supported. Tried other options like maxover, rank, etc.
This is what I’m using for the dropdown which works perfectly (extract function gives errors)-
concat(substring(formatDate({REPORT_DATE}, ‘MMM-dd-yyyy’), 8, 4),‘/’,substring(formatDate({REPORT_DATE}, ‘MM-dd-yyyy’), 1, 2))
Next, I need to create a column which will give maximum (one single value) for this field that I can assign as column default to the parameter. Would you know how?
@Ops_Expert
I misunderstood your question. I thought you wanted to find the date that your dataset is refreshed and use it as default in your filter.
The reason why the extract function doesn’t work is because it returns an integer, so you have to convert the integer to a string before using concat.
Max of a string isn’t supported but you can find the max of the date field before you convert it to string.
There’s no way to assign a single dynamic value for the parameter default. You have to create a dataset where every row represents a user or group of users and specify the default for every user or group. If you look at the link that I shared, you’ll see an example. In this example, the user with username anacarolinasilva sees “NorthEast” as the default value for the region field.
In that dataset you’ll have to calculate the max date and convert it to a string. Since it’s the same max date that applies to all users, you’ll just repeat it in every row like this:
Add a refresh schedule to the dataset so that the max date is kept up-to-date. Then add the dynamic default to your parameter.
Select the dataset that contains your default values, the column that identifies your user or group and the column that contains the default values.
We decided to use date parameters since text conversion caused lots of issues. Following that I was able to solve the other issue by using a parameter with default date set to today and adding top 1 condition : custom filter < = today. That enabled latest date selection.
Thanks for your input anyways!
