Train Quicksight Q topic to correctly filter data with weekly / monthly snapshots

I have created a quicksight topic that uses a dataset that contains data already aggregated at a weekly and monthly level. I have a field in my dataset ‘timeframe’ which contains values (‘weekly’,‘monthly’) and another field ‘snapshot_day’ which contains the week start date or month start date depending on whether ‘timeframe’ is weekly or monthly.
The challenge is that when we ask Q a question like ‘what is metric x for 2024’ or ‘what is metric x for August 2024’, it doesn’t filter for timeframe = monthly but aggregates all the monthly and weekly snapshots in the time range.
I tried applying dataset level filter (timeframe = monthly) with setting ‘Apply always, unless a question results in an explicit filter from the dataset’ but then Q always applies this filter even when user asks for weekly aggregation e.g. ‘What is metric x by week for 2024’
How can I train the topic to correctly filter the data based on user question?

Is there a way to give general instructions like above to a topic (e.g. use field timeframe to filter for weekly / monthly snapshot)? I also want to give other topic level instructions like certain abbreviations / synonyms which are relevant to all fields in the topic.

Hi @taliyan,

Can you try creating 2 separate datasets - one for weekly data and the other one for monthly data? Add both datasets to your topic and set the time basis to weekly and monthly respectively. I saw this recommendation somewhere but I can’t remember where. Based on how you ask a question, QuickSight will be able to figure out which dataset to use to get the answer - at least that’s what I think the article said…

1 Like

Thanks David, I will try that. Do you have a solution for giving topic level instructions like certain abbreviations / synonyms which are relevant to all fields in the topic?

Hi @taliyan,

Unfortunately, you’ll have to add the synonyms to the fields in each dataset separately.

1 Like