Troubleshooting "Non-aggregated Field" Errors in Amazon QuickSight WoW Analysis

Creating Week-over-Week (WoW) analyses in Amazon QuickSight can be incredibly valuable for tracking performance and identifying trends. However, you might encounter the frustrating "non-aggregated field" error while building your calculations. This article breaks down the likely cause of this error in the context of WoW analysis in Amazon QuickSight and offers a potential solution.

Understanding the "Non-Aggregated Field" Error

The core issue is that you're likely trying to combine aggregated (calculated across multiple rows) and non-aggregated (individual row-level) fields within the same calculation. QuickSight needs a consistent level of aggregation for calculations to work correctly.

Scenario: Building a WoW Analysis

The user in the original query was building a WoW analysis, suggesting they were trying to compare current week's data to the previous week's. Standard WoW calculations often require comparing aggregated metrics (like sum(actuals)) with a lagged version of the same metric (previous week's figures). [Internal Link: Insert link to a relevant QuickSight tutorial/article on your website here, e.g., "Creating Basic Calculations in Amazon QuickSight"].

The Suggested Solution: sum(cube_actuals)-lag(...)

The proposed resolution, sum(cube_actuals)-lag(...), directly addresses the aggregation conflict. Let's dissect how it works:

• sum(cube_actuals): This part calculates the sum of the cube_actuals field. This aggregates the data, resolving the row level data to aggregate level.
• Lag Function: The Lag function shifts data from a prior time period to the current row. It essentially retrieves a previously calculated aggregated value. External Link: AWS QuickSight Lag function documentation

Why This Works

The lag function returns an aggregated value, making both sides of the subtraction operation aggregated. This allows QuickSight to perform the calculation without throwing the "non-aggregated field" error.

Example

Say field_A is a non-aggregated field, and you want its value shifted by 1 in time

lag(sum(field_A), [Date], 1) will give you your desired results

Where Date is the field on which the rolling sum is computed and the third argument is the offset

Key Takeaways

• The "non-aggregated field" error in QuickSight arises when mixing aggregated and non-aggregated fields in a calculation.
• For WoW analysis, ensure that both the current period's data and the lagged (previous period's) data are aggregated.
• The sum(cube_actuals) - lag(...) approach is a common and effective way to resolve this error in WoW calculations.

Further Troubleshooting

1. Verify Field Types: Double-check that all fields involved in your calculation are of compatible data types (e.g., numeric).
2. Aggregation Levels: Explicitly define how each field should be aggregated (sum, average, count, etc.).
3. ifelse Statements: If using ifelse statements, ensure that both branches return values at the same aggregation level.
4. Look at the documentation: Refer to the online documentation to see what prebuilt functions are available for your specific data types

By paying close attention to aggregation levels and using the sum(cube_actuals) - lag(...) suggestion, you can overcome the "non-aggregated field" error and unlock the power of WoW analysis in Amazon QuickSight.