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.
