Date filters

Question & Answer
,
1

Hi QS community,

Is it possible to create a filter which displays the year (s), and when a particular year is selected (i.e. not all years), the individual months (or even Q1, Q2, Q3, Q4) are displayed, which the user can then select from?

Right now, I have a year filter, and also created parameters for the start and end date (but these two aren’t related).

image

Our test users have said they would prefer the ability to select a particular year and then the specific month(s) or quarter(s), rather than having to manually enter a start and end date.

Is there a simple way to do this?

Thanks.

2

Hi @jotg

You could look to make a calculated field to change your dates into strings and then look into cascading filters.

1 Like
3

Hi @Max ,

The admission year filter is using a calculated field which is a string, using this script:

image

Should this script now be edited to include the month?

And would this then be: toString(extract(‘MM’, ‘YYYY’,{429Dateofadmission}))

Thanks for your assistance.

4

Hi @jotg

No I would make three separate calculated fields.

One for year, quarter, and month.

year : toString(extract('YYYY',{429Dateofadmission})
quarter: concat('Q',toString(extract('Q',{429Dateofadmission}))

For month you would have to make a large ifelse statement if you wanted the actual names. You could also look to do the month in SQL instead of QuickSight because SQL has more functionality that could give you this without writing so much.

month: ifelse(extract('MM',{429Dateofadmission})=1,'Jan',extract('MM',{429Dateofadmission})=2,'Feb',etc..

5

Hi @Max,

Thanks so much. Your scripts were very helpful.

6

Hi again @Max,

Is there a way to modify the quarter script so that Q says Null?

image

Thanks

7

Sure.

ifelse(isNull(extract('Q',{429Dateofadmission}),NULL, concat('Q',toString(extract('Q',{429Dateofadmission})))
2 Likes
8

That’s great. Thanks @Max .

1 Like