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

Question & Answer
,
1

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.

2

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…

image
image988×342 14.3 KB

1 Like
3

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?

4

Hi @taliyan,

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

1 Like
5

I tried this approach but it did not work. Q is not able to get the answer from the right dataset.

6

I think your best bet is to use a dataset which is at a daily level and let Q aggregate it for you at a weekly or monthly level based on how you ask your question. If you don’t have such a dataset, you can try with a dataset that just contains your weekly snapshots and again let Q aggregate the data at the monthly level for you.

7

Hi @taliyan,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

8

Hi @taliyan,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information if needed.

Thank you!