I need your assistance on a calculated field I am creating in Amazon Q Topics:
I am creating multiple fields for our metrics, here are 2 examples:
Plan NPT Hours: sum({Scheduled Npt Minutes})/60, the result of this is showing as currency, we can’t manage this data as currency as it is a time sensitive data
And here is the other metric: Plan NPT%: sum({Scheduled Npt Minutes}) / sum({Staff Min})
This second metric needs to be shown as a %, and is being captured as a currency, all the fields in the backend have been configured already.
The question is how can we do for this metrics to be shown the first one as time and the second one as % and not to be considered as a currency?
Thanks a lot for the quick response, the issue is that the other semantic type options are not being displayed, is displaying currency as default and not allowing me to show other options.
Please see the attached screenshots
Feel free to reach me on chime or slack either if it is easier
Formulas used:
Plan NPT%: sum({Scheduled Plan Npt Min}) / sum({Net Staff Min})
Scheduled NPT Hours: sum({Scheduled Npt Minutes})/60
The type of fields used for the calculations have been set up in the backend already
Hi @dmmorer,
Try ignoring the semantic type and leave it blank; you can still change the format to match your preferred output. Are you not receiving the ‘more formatting option’ in that dropdown window?
Hi @dmmorer,
Yes, this should properly show your values in the correct format when those fields get utilized in a question’s response. If you’re running into an issue where your question is pulling the wrong field to run a calculation, you may need to adjust your question format, for instance to include the word ‘Schedule’ or ‘Hours’ when looking to utilize ‘Scheduled NPT Hours’.
Thanks a lot for the help on this, I will finish creating all the fields and start running some testing.
When you mean adjusting the question format, you mean how they will ask the question or is there like an style or format of questions we can use for certain scenarios?
Hi @dmmorer,
No problem at all, hope the testing is successful!
I wouldn’t necessarily say there’s a certain style or format per scenario but it does utilize each word in a question to determine the most likely fields you’d want utilized in the answer; which is where adding synonyms for fields helps out. It can take a lot of finessing if you have like fields/field names, I’ve found the most success in these from monitoring the format other users are asking questions in, then adjusting synonyms based on the user activity. Once you’ve verified an answer, you can add question variants as well which helps as well:
Thank you so much, after testing yesterday the data is displaying on the correct format.
However, I do have one additional question, these calculated fields we are creating, on the analysis the type of aggregation appears as custom, these are %, therefore it needs to be a custom, not a sum, count, average, etc, I have left the default aggregation in blank, and restricted sum, count and count distinct for these calculated fields, however, I will like to know, leaving them like this will equal the custom aggregation the analysis generates?
Because it is a percentage so we need to have the same data Q reflects as the one shown in the analysis.
Hi @dmmorer,
So when you use the field in an analysis, you would normally receive the ‘custom’ tag for aggregation as the aggregation is defined within your calculated field. If you setup a calculated field with a ‘sum’ function for instance, the ‘custom’ aggregation will just be sum.
So if you define the function that’s taking place within your calculated field, that will be the aggregation that takes place within your topic when it’s utilized.
Let me know if this makes sense or if you have any additional questions!
Thanks a lot for the help, yes the aggregation worked as custom and is displaying the data in the expected format.
However, I am facing another issue with Q, let me know if you can help me through the same case or if should open a new one.
I will leave it here anyway, so there is one filter that is excluding null values, right now, there is a column used to filter out data that is generating blank values, the issue is that, in the analysis it is capable of converting these blank values to null values, and it excludes the data which is the expected, however, Amazon Q is not able to process this blank values into null values and it includes them in the results as blank values, generating a discrepancy between the data shown in the analysis and the data provided by Q, and the data for Q would be inaccurate because this data should be extracted.
This issue is happening with all the filters that exclude null values
So, is there a way to make Q exclude does null values as the Analysis does?
