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!
similarly, I have issue, I need date filter should be in dropdown … what I have done is create a calculated field startdate_string substring(toString(startdate),1,10) and make the startdate_string in filter so dropdown this is solved.
the next issue is i need to make default latest value … what I have done is create a parameter with default value 1 and I have made into filter so the latest month only it is showing as default but…
after this the startdate_string is still showing like selected all not the latest value alone it is showing …
so two thing is issue
one is I coudn’t select “selectall“ option \
and another is by default it is showing latest date but it is not reflecting in startdate_string filter in that it is like selected all
