Encountering errors while building your WoW (Week-over-Week) analysis in Amazon QuickSight can be frustrating. A common issue users face is the "non-aggregated and aggregated fields together" error. This article aims to clarify why this error occurs and provide a practical solution to get your WoW analysis back on track.
The root cause of this error lies in the way Amazon QuickSight handles different types of fields within calculations. Simply put, you can't directly compare or combine fields that are already aggregated (like sums, averages, or counts) with fields that are not aggregated (individual data points).
This often happens when trying to use functions like lag (for calculating previous week's values) in conjunction with aggregated fields, especially when dealing with calculations spanning different time periods in your analysis. It’s a common stumbling block for both new and experienced QuickSight users.
lag in sum()The key to resolving this error lies in ensuring all fields within your calculations are treated consistently—either all aggregated or all non-aggregated.
Here's the recommended approach, based on insights from the Amazon QuickSight community:
Instead of:
cube_actuals - lag(cube_actuals,1,[week])
Try:
sum(cube_actuals) - lag(sum(cube_actuals),1,[week])
Explanation:
cube_actuals: This is likely your field containing the actual data you're analyzing (e.g., sales, website visits).lag(… ,1,[week]): This function retrieves the value of the specified field from the previous week. In this case, the field that is having issues.sum(cube_actuals): By wrapping cube_actuals inside sum(), you're explicitly telling QuickSight to aggregate this field before performing the calculation. This ensures all components of the formula are treated as aggregated values.lag(sum(cube_actuals),1,[week]): The lag function will then similarly access the aggregated sum for the previous week.[week]: Specifies the partitioning by week for the lag calculationImportant Considerations:
sum()) aligns with the granularity expected for your WoW analysis. If you need to work with daily or hourly data, adjust the aggregation function accordingly.If the sum() trick doesn't immediately resolve the issue, consider these additional troubleshooting steps:
cube_actuals field and any other fields involved in the calculation are appropriate (e.g., numeric types for aggregations).ifelse(isNull(cube_actuals), 0, cube_actuals) to handle nulls gracefully.While focusing on sum() to fix aggregation issues, remember that QuickSight offers a range of aggregation functions:
avg(): Calculates the average of a measure.min(): Returns the minimum value.max(): Returns the maximum value.count(): Counts the number of rows or distinct values.Choose the appropriate aggregation function based on your specific analytical needs.
Encountering the "non-aggregated and aggregated fields together" error in Amazon QuickSight can be a hurdle when building your WoW analysis. By understanding the underlying cause and applying the suggested solution – wrapping relevant non-aggregated fields with functions like sum() – you can overcome this issue and create insightful visualizations. Remember to review your data types, handle null values, and break down complex calculations for effective troubleshooting. This will enable smoother data exploration within QuickSight.
