Creating dropdown control type for datetime type columns

Today in Amazon QuickSight you only have an option to choose date picker as a control type for a datetime data type column. Now if your dataset has a column called Quarter End Date for each row in your dataset, you have to navigate through the calendar to select a date in the past i.e. 30-SEP-2020. It might take quite some time and clicks/typing to choose this date in the date picker control.

image

In this article you’ll learn how to show dates in a dropdown control type, through which you can control the list of values available for selection. It also makes it easier for the users to select a particular date.

Desired result:

image

Step 1: Create a String type equivalent dataset field for the datetime type field
In QuickSight, you can only have an option to choose dropdown control type if the datatype of the column in string. Lets say you have a dataset which has column called Quarter End Date. You have to create a new column in your dataset that maps your date column to a string type field. You can easily do that in your dataset using Calculated Fields.

Note: It is important to create the calculated field in the Dataset and not the Analysis if you want to tie a Parameter Control to the value of this field. If you do not need to use the values in a parameter (e.g. you only need a basic filter control) then you can alternatively create the calculated field in the Analysis too.

For example, in your dataset for column Quarter End Date that is of datetime datatype, you can create a calculated field called Quarter End Date String that maps to a string type equivalent for each value in Quarter End Date column.

Quarter End Date String : formatDate({Quarter Ending}, 'yyyy-MM-dd')

Note: If you want to just filter based on the values of the Quarter End Date String, you can simply create a filter by selecting a visual and adding a filter along with a control (by selecting ‘Add to sheet’ from your filter) for your new calculated field. But if you want to use the values selected by the user in calculated fields or insights, keep following this article.

Step 2 (optional): Create a parameter
In your Analysis, you can now create a parameter called QuarterEndDateParam with Data type as String and Values as Single value. You can also choose a Static default value, typically this value will be your latest Quarter End Date in the dataset. Then click Create.

Step 3 (optional): Create a control for the parameter
Once the parameter is added, you can create a control for the parameter. While creating the control choose Dropdown as Style. Select Link to a dataset field, then choose the right dataset and choose Quarter End Date String when presented Link to a dataset field. Click Add.
Now you can see the new control on the sheet with a dropdown of all the Quarter End Dates in your dataset.
You can now test by selecting one of the values in the dropdown and validate against your source data.

image

Note: It is very likely you want to have some calculated fields built based on the user’s selection of the Quarter End Date. If so, keep following this article.

Step 4 (optional): Create calculated fields
Lets assume that you want to have a visual that shows the number of accounts for a segment for current quarter, previous quarter and previous year. You can derive the values for previous quarter and previous year using the respective calculations.

Accounts for current quarter : countIf({Quarter End Date}, {Quarter End Date}=parseDate(${QuarterEndDateParam}))

Accounts for previous quarter : countIf({Quarter End Date}, {Quarter End Date}=addDateTime(-1,"DD", addDateTime(-3, "MM", addDateTime(1, "DD", parseDate(${QuarterEndDateParam})))))

Accounts for previous year : countIf({Quarter End Date}, {Quarter End Date}=addDateTime(-1, "YYYY", parseDate(${QuarterEndDateParam})))

Step 5 (optional): Putting it all together
Now that you have all the calculated fields in place, you can create a visual by selecting the required fields and calculated fields.

4 Likes