Quick Sight Q Topic not applying relative date filters for last 7/15 days and results not showing correctly

Q&A
1

HiI am working with Amazon Quick Sight Q Topic where I want to create verified questions for Charges, Payments, and Adjustments in the last 7, 15, and 30 days.

My dataset has a field charge service date along with Charge Amount, Payment Amount, and Adjustment Amount.I have created the calculated fields for 7,15 and 30 days timeframe and also charges, payment and adjustments amounts to be displayed.

I tried creating verified questions like:

Total charges, payments, and adjustments in the last 15 days.
Total charges in the last 7 days.

However, when I test in Q, the filter for last 7 or 15 days is dropped automatically and Q shows results without applying the date condition (screenshot attached).

image
image1251×446 62.6 KB

Actually there is no date for 15 days and how 15 days is getting calculated : Created calculated fields using ifelse(dateDiff(ChargeServiceDate, now(), ‘DD’) <= 15, Charges, 0) but Q seems to ignore this and not showing the desired result. same goes for 7 days and 30 days for Charges,payments and adjustments.

Also in custom instructions i have added : When a user asks for Charges,Payment and adjustments total in a timeframe (like last 7, 15, or 30 days) data, do not drop the date filter even if no results are found. Instead, return 0 or ‘No records available’.
So it should show ‘No records available’

I have also used the flag for relative time frames.ifelse(

dateDiff(ChargeServiceDate, now(), ‘DD’) <= 15,

‘Yes’,

‘No’

)

Expected behavior:
When I ask “Total charges in last 15 days”, Q should apply a relative date filter (ChargeServiceDate: Last 15 days from today).

Actual behavior:
Q drops the filter and shows total values for all time.

Questions:
1.Is this a limitation of Quick Sight Q Topic with dynamic relative dates (last X days)?
2.What is the best practice to implement rolling windows (7, 15, 30 days) in Q Topics for verified questions?Calculating from current date- charge service date for all (7,15,30 days time frame)

2

Hi @rahul8,
A couple things to check here:

  • It looks like it’s ignoring any type of time field when asking that question, first want to confirm that you have a date field marked to be included in your topic?
  • In regards to your relative date calculation, did you setup as another field included in your topic or as a filter?
  • If both date fields are included, the topic may have a hard time differentiating which one you intend to use; this could be adjusted through the use of synonyms.
  • Have you tried asking the question without creating calculated fields for your time windows? I wonder if the topic is able to provide you with last 15 days of results when just included the original date field.
1 Like
3

Hi @Brett

I have provided the specific instructions that date should be calculated from the charge service date and do not drop the filter incase there is no data show the no records.

Also it is working for the 30 days window in the similar fashion, but not for 7 and 15 days.
without the timeframe it gives the overall sum of the amount.
Could you please suggest me what to do for that to be resolved?

4

Hi @rahul8,

Hope everything is well with you! Just checking in since this thread hasn’t received a response in a while. Were you able to find a solution to your question in the meantime or are you still facing persistent issues? If we do not hear back within the next 3 business days, I’ll go ahead and close this topic.

Thanks!

1 Like
5

Hi @rahul8,

Since we have not heard back, I’ll go ahead and close out of this topic. However, if you have any additional questions, please feel free to post again in the Quick Suite Community and link this thread for any relevant information!

Thank you!