I need to create a Custom Date Range control in Quicksight dashboard

Question & Answer
, , , ,
1

I have a requirement to create a date range control. The values in the control should include options for 1 day, 1 week, 30 days, 3 months, 6 months, 1 year, Year To Date (similar to the “Year to Date” option in the relative dates filter), and Month To Date (similar to the “Month to Date” option in the relative dates filter). Based on the selected option, the visual should change accordingly, and there should be a start date-time and end date-time control that should also change based on the options selected in the date range control. I have attempted to find a solution through multiple sites without success. I am hoping to receive assistance here.

2

Hello @teja1, welcome to the QuickSight Community!

In regards to this question, there isn’t a built in solution to resolve this, but I have accomplished something very similar. In my use case, I set up my dataset with the data I required for the visuals that I wanted to filter this way. Then, using WHERE clauses and UNION, I split the returned data into groups with dateType string field value.

I selected the fields I wanted, added “3 Months” as DateType, then in my where clause used this:
where date >= dateadd(day, -90, CURRENT_DATE())

dateadd and CURRENT_DATE might be slightly different for you depending on the database you are linking to QuickSight, but this is the easiest way I have found to manage this. Once your dataset is created with each of the DateType groups you need for filtering, you can use that for your control values to filter your data how you want.

I will mark this as the solution, but if you have any follow-up questions about creating this dataset, please let me know!

1 Like
3

Hello @DylanM,

Thank you for your prompt response. I appreciate the insight you provided. However, I would greatly benefit from more detailed guidance on the specific steps involved in setting up the dataset and implementing filters for Month to Date (MTD) and Year to Date (YTD) I mean the logic in the context of the AWS Timestream Database.

If possible, could you provide additional information or examples to help me better understand the process? I’m particularly interested in the nuances related to the AWS Timestream Database.

Your assistance is highly valuable, and I look forward to any further insights you can share.

Thank you!

4

Hello @teja1, from the datasource that you have linked in QuickSight you will need to create a new database and build it from custom SQL.

At minute 1:18 of this video, it shows creating a dataset based directly off a schema from your datasource. Rather than selecting it directly, you want to click the Use custom SQL button instead so you can write the query manually.

Then you can query the fields that you need from your datasource using a Select statement in SQL.

Give it a name like this:

with base_query as (
select
userId,
name,
category,
etc
from database table
)

Now you can take those fields and split them into categories:

,edited_query as (
    select *,
    '3mo' as filter_time
    from base_query 
    where date >= dateadd(day, -90, CURRENT_DATE())

    UNION ALL

    select *,
    '6mo' as filter_time
    from base_query 
    where date >= dateadd(day, -180, CURRENT_DATE())

    UNION ALL

    select *,
    'YTD' as filter_time
    from base_query 
    where year = (year(current_date))
)

select * from edited_query

That is a rough idea of how you will build that in your SQL. I’d recommend looking into UNION statements and make sure you are pulling all the necessary data fields from your database.

Here is some documentation on creating parameter controlled filters in QuickSight as well.

1 Like
5

Hi @DylanM ,

Thanks a lot for your help! I appreciate the detailed guidance on creating the date range control in QuickSight. I’ll check out the resources and follow your suggestions.

1 Like