Using three datasets into one

Question & Answer
, , , , ,
1

Hi Team,

I have three datasets: an hourly dataset, a daily dataset, and a monthly dataset. The hourly dataset contains data for the current date only, the monthly dataset contains data for the 1st and last date of each month, and the daily dataset contains data for each day. All datasets have the same columns. Currently, I utilize Custom SQL in Quicksight with Direct Query mode instead of SPICE, and the data source is AWS TimeStream. I have three parameters: companyid, start date, and end date. If the user selects yesterday as the start date and today as the end date, yesterday’s data will be in the daily dataset and today’s data will be in the hourly dataset. If the user selects today as both the start and end date, I need to query the hourly dataset. Similarly, if the start date is January 1, 2024, and the end date is January 31, 2024, then it should query the monthly dataset.

Essentially, I need to combine these three datasets and query the specific dataset based on the start date and end date parameters. And there is a need for separate parameters from each dataset for example hourly start date parameter, hourly end date parameter
daily start date parameter, daily end date parameter
monthly start date parameter, monthly end date parameter
The value of the parameters should be changed according to the parameters of start date and end date parameters.
How can I achieve this?

2

Hi @teja1 - One option you might explore is to create a new parameter Parameter 3 that will be controlled via Navigation Action and a calculated field.
The calculated field will contain the value based on existing parameter choices and the navigation action will set parameter 3 to help decide which visuals to display. A similar topic was discussed here that you can use as reference.