How to apply filter on a string field which is storing dates

Question & Answer
, , ,
1

I have a date field but filters are not working on it (contacted aws support and they are investigating it) to achieve the work for now I plan on making the field as a string using toString( ). and then I am trying to apply a filter on it but can’t figure out how to do it. This is what I am using currently:
Filter Type: Custom filter
Filter Condition: Starts with
Value : having a date in this format = yyyy-mm-dd

2

Hi @haider , Instead of doing that you also preferer another options by creating one calculated field as Date to string type.

We have a function available in QuickSight as formatDate which is converted the date field into string field so simply you can use the **formatDate function in QuickSight.

For the formatDate the return type is string.

Syntax - formatDate(Your Date Field, ‘yyyy-MM-dd’)

Now you can get your date field as String Field you can simply now use you field into the filter.

Please follow the supported Date Format available in QuickSight.
I am adding below the link for understanding in more details.

3

hi, thank you for the advice.
I wanted to ask if it is supposed to be double quotes or single quotes around the dates.
formatDate(Your Date Field, ‘yyyy-MM-dd’) gives me error but
formatDate(Your Date Field, “yyyy-MM-dd”) does not give any error

4

yes it is single quote only.

Thank You

5

okay, thanks the single quotes worked. but the filter is not working. do you have any idea why it wouldn’t work?
For the filters I have this setting:
Filter Type: Custom filter
Filter Condition: Starts with
Value : having a date in this format = yyyy-mm-dd

6

I would suggest creating a table visual and putting original date column and newly created calculated column to inspect the data and results of the calculated column. I do this a lot when I create complex calculated fields.

After you see what’s in your data and results of the calculated field expression, you should be able to specify filter values and can delete the table visual that you no longer need.

2 Likes
7

Hi @haider why you are selecting the Filter Type as Custom Filter please select the Filter Type as Filter List

I guaranty you after doing this you can achieve you result.