My QuickSight analysis has a dataset with a year field. It also has a multiselect date control that’s populated with those year field values. Is there a way that I could dynamically default the control to select both the latest year and the previous year for anyone who visits the dashboard? See the screenshot below:
To get the latest and previous year of a date field, I created a calculated field:
ifelse(extract(“YYYY”,{DateField}) = maxOver(extract(“YYYY”,{DateField}),,PRE_AGG) OR extract(“YYYY”,{DateField}) = maxOver(extract(“YYYY”,{DateField}),,PRE_AGG) - 1, extract(“YYYY”,{DateField}), NULL)
In the dynamic default screen, I would use that calculated field as the “Column for default value,” but I’m not sure what to select for the “User name column” or “Group name column.” Thanks for the help
Hello @Adam_W, welcome to the QuickSight Community! My first question for you would be do you want users to be able to skip some years? If not, I think the easiest solution would be that you can create 2 Parameters for a Start Year and End Year. You can set default values in each Parameter. Start Year will be “Start of Last Year”, and End Year will be “End of This Year” or you can set to Relative Date “Today”. I’ll screen post a screenshot of that part below:
Now you can create your Controls from each parameter to just allow a user to select the year they want if they want to change them. Here is what that will look like:
Now since the parameter exists with a datetime field up to the minute, I’m not sure directly fitlering by the Year Date field will work exactly right, but it might. You can try to fitler that field and use the StartYear param as the Start Date and EndYear param as the end date.
Another option would be to create an ifelse statement like this: ifelse(truncDate('YYYY', ${STARTYEAR}) <= {DateYear} AND truncDate('YYYY', ${ENDYEAR}) >= {DateYear}, {DateYear}, NULL)
Now you will always get your default values and the user can select a grouping of years! Let me know if that helps.
