Calculated field is not providing expected outcome in AmazonQ QuickSight Topic

Question & Answer
, , , ,
1

I have a specific use case where I am trying to get the percentage of people meeting a handle time goal (PPMG metric), I have used the same logic to create the metric in QuickSight dashboard but in AmazonQ topic, I am not getting the data as expected while. I am using the below logic in calculated field,

ifelse(sumOver(sum({Handle Time Without Outliers})/sum({Resolves Without Outliers}),[{Function Name},Login,{Week number}])<=sumOver(sum({Handle Time Target})/sum({Resolves Without Outliers}),[{Function Name},Login,{Week number}]),distinct_count(Login),0)/distinct_count(Login)

this is not giving any output. Please note that my dataset has multiple records at people/associate level at week granularity and I am trying to get a combined result at a week level. The above calculation is working fine with dashboard but not working as expected in AmazonQ topic. Looking for a suggestion here

2

Hi @ayantik and welcome to the QuickSight community!

By not giving an output, is it giving incorrect information or is it just returning a screen that says it cannot answer your question?
A couple preliminary questions:
How did you name the calculated field and what question did you ask? Sometimes that can affect whether the system understands to use your calculated field or not based on your language.

But beyond that, since the calculated field is working in your dashboards (and being accepted without errors when built in your topic I presume), my initial thought is that it may have something to do with how the fields being utilized in the calculation are setup in the topic.

A couple things to confirm in the ‘Data Fields’ section:

  • Are there any filters applied to the topic that could affect the calculated field?

  • Are all of the fields being utilized in the calculation marked as ‘included’
    image

  • Is each field role setup correctly as a measure or dimension?

  • Are the aggregation types setup to hinder your calculation?

image
image1226×140 7.1 KB

Let me know if any of these suggestions help for your case or if you have any additional questions!

3

Hi @Brett thanks for your reply, please find the answer below,

  1. It’s showing as “INVALID_DATAPREP_SYNTAX, GENERIC_DATA_SOURCE_EXCEPTION” while I am running a value preview. Also while asking questions about this field, I am getting tis exception “Your question cannot be answered.”
  2. The calculated field is named the same which I used in the question and I can see it’s mapping the correct field,
  3. I have no filters applied,
  4. all the fields used in the calculation are marked included,
  5. roles are set up correctly.
  6. It might be an issue with aggregation I believe but I am not able to identify.
    here’s the snippet of the field set up,
    image
    image1657×455 39.4 KB

    let me know if you have encountered anything like this or any suggestions?
4

Hi @ayantik,
Apologies I missed your last response! So receiving that error in the value preview section is not abnormal, there are scenarios will you will encounter that but when asking a question, the calc. field will work as expected, but receiving a response like ‘your question cannot be answered’ is a bit more concerning.

By chance, do you have any of the fields being used in the calc. field turned off in the topic?

Out of curiosity, how are you wording the question when receiving the ‘cannot be answered’ prompt?

5

Hi @ayantik,
Following up here as it’s been awhile since we last heard from you. Were you able to find a work around for your case or are you still working on this?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi Brett,

It has been resolved. The issue was with the calculation logic, you can close it. Thanks for the response.

1 Like