Dynamic Date Parameter

Question & Answer
,
1

Hi, Community. I have a visual that I would like the reader to determine the start period displayed on the graph based on a selection (shown below). The reader has the option to select: Year, Quarter, Month. The start of each period would be dynamic based on the current date; i.e. assuming it’s Dec 15th, selecting Quarter would display a date period from October 1st to the end date parameter (relative parameter defaulted to yesterday).

QuickSight provides a relative filter parameter based on the current date but I’m unable to configure a dynamic toggle, similar to a calculated field. The parameter would ultimately then be passed into the filter start date value. From a functionality standpoint, having the ability to pass a calculated field with a scalar value into a parameter.

image

2

You can look at parameters as well as the truncdate function.

A calculated field could look like this.

ifelse(${date_param}=‘Month’,truncdate(‘MM’,{date_field}),‘Quarter’,truncdate(‘Q’,{date_field}),truncdate(‘YYYY’,{date_field}))

Then I would filter based on this field.

1 Like
3

Hi, @Max, I think the challenge is I’m applying a post aggregate filter (identical to step 10) to an Area Line Chart. I’m not sure how I can incorporate this suggestion in combination with that filter. As I mentioned, the parameters provide a out-of-the-box solution, but limited.

4

Sorry, let me know if this is what you’re looking for.

Can you check it against a start date parameter? Is that what you’re looking for?

ifelse(${DateParam}=‘Month’ AND truncDate(‘MM’,{date_field})=truncDate(‘MM’,${start_date_param}),‘True’,${DateParam}=‘Quarter’ AND truncDate(‘Q’,{date_field})=truncDate(‘Q’,${start_date_param}),‘True’,truncDate(‘YYYY’,{date_field})=truncDate(‘YYYY’,${start_date_param}),‘True’,‘False’)

And then filter on this field to have a custom filter of only being ‘True’.

This is checking to see if the date fields in your dataset are in the same period as your start date param.

6

Hi @acharchar
Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

7

Hi, @gillepa, @Max , the suggestion functions but does not resolve the issue. The suggestion needs to be a post agg filter type. The filter I’m using on my graph is the following:

minOver(min{date_field}),[{date_field}],POST_AGG_FILTER)

This post agg filter is essential for this visual and documented in the link provided above. I’m looking to take the visual one step further by allowing the reader to see overlaid revenue over different periods (month, quarter, year)

Here is that filter applied to graph. Aforementioned, the end date uses a relative period but the start date parameter does not provide a more dynamic solution.

image