Same calculated field displaying different results in different visuals

Hello,

I’m new to QuickSight and figuring out how to use calculated fields. I’m sure I’m not doing this in the most efficient way so any advice would be greatly appreciated.
To summarise, I’m doing a visualisation based on our team’s tickets and KPIs associated with these.

Firstly, I have created calculated field 1 which calculates the difference between two dates.

I am then calculating the total average days for difference in days for different levels of Customers using calculated field 1. As a result I have created two calculated fields for the total average for each Customer type. Let’s call these calculated field 2a & 2b.

I then calculate the final metric, which is calculated field 3: 1-(calculated field 2a/Calculated field 2b)*100 which gives me a percentage.

Calculated field 3 displays correctly in the KPI visual of my dashboard, however when I try to do a trending report for each quarter displaying calculated field 2 a & b as bars and calculated field 3 as a line over the top of these averages, calculated field 3 displays a different result.

Thanks in advance for your help.

  • A

Hi @Aj_Nicholls

Based on the information you provided, it seems that you’re encountering a discrepancy in the results of Calculated Field 3 when creating a trending report for each quarter. The issue could be related to the way QuickSight handles calculations and aggregations in different visualizations.

To troubleshoot this, I would recommend the following steps:

  1. Verify the aggregation settings: Make sure that the aggregation settings for Calculated Field 2a and 2b are appropriate for your visualization. For example, if you’re using a bar chart to display quarterly averages, ensure that the aggregation is set to average for both fields.

  2. Check the data types: Double-check that the data types of the fields used in the calculations are consistent and accurate. Any inconsistencies can lead to unexpected results. For example, if the date fields used in Calculated Field 1 are not recognized as dates, it could affect the calculations.

  3. Validate the formulas: Review the formulas for Calculated Field 2a, 2b, and 3 to ensure they are correctly capturing the logic you intend. It’s possible that a mistake in the formula could result in inconsistent results across visualizations.

  4. Test with a simpler setup: Create a simplified version of your report and visualization with a smaller dataset to isolate the issue. This can help identify if the problem lies in the specific configuration or if it’s a more general problem.

  5. Consider alternative approaches: If the issue persists, you might explore alternative ways to achieve the desired results. QuickSight offers various features and options for calculations, such as level-based aggregations, aggregations within visualizations, or custom SQL queries

hope helps

1 Like

Hi @Aj_Nicholls

Hope you are able to resolve this issue . If we do not hear back in the next 3 days, we will archive the question.

Thanks for posting your questions on the QuickSight Community Q&A Forum!