Creating Week-over-Week (WoW) analysis in Amazon QuickSight can be powerful for tracking trends. However, users often encounter the frustrating "non-aggregated field" error when constructing calculated fields. This article provides a breakdown of how to resolve this issue, specifically focusing on the common scenario where you're using the lag function.
The "non-aggregated field" error in QuickSight arises when you attempt to combine aggregated and non-aggregated fields directly within a calculation. Aggregation functions, like sum, average, min, or max, summarize data across multiple rows, while non-aggregated fields represent individual row values. QuickSight needs consistency in the type of data being processed, so mixing these types leads to this error.
lag FunctionA typical WoW analysis involves comparing a metric's current value to its value from the previous week. The lag function is often used to retrieve the previous week's value. Because lag effectively operates on an aggregated level (retrieving a value from a group of rows representing the prior week), it's treated as an aggregation function.
lagThe key to solving the "non-aggregated field" error when using lag is to ensure all components in your calculation are aggregated. In the context of the initial problem, the user was likely trying to subtract a non-aggregated field directly from the result of the lag function.
The suggested solution, sum(cube_actuals)-lag(...), addresses this by wrapping cube_actuals (presumably the field representing the current week's value) within the sum aggregation function.
Here's a breakdown of why this works:
sum(cube_actuals): Aggregates the cube_actuals field. Even if the data is already at a weekly level, explicitly using sum ensures QuickSight treats this as an aggregate. If you have other dimensions that need to be considered, be sure your level-aware calculations are set up properly. Refer to Amazon's documentation on level-aware calculations for more info.
lag(...): Retrieves the aggregated value from the previous week, inherently acting as an aggregation.
sum(cube_actuals) - lag(...): Now you're subtracting an aggregate (the result of lag) from another aggregate (sum(cube_actuals)), which resolves the error.
Let's say you want to calculate the WoW difference in website visits:
To calculate the WoW difference, you would use the following calculated field:
sum(visits) - lag(sum(visits), 7)
This formula first aggregates the visits using sum and them calculates the difference between the sum of the current week and the sum of the previous week using the lag function. Find more details on using lag function.
lag function is treated as an aggregation function.lag in a WoW analysis, ensure all fields involved in the calculation are aggregated using functions like sum, average, etc.By understanding the cause of this error and applying the provided solution, you can effectively build WoW analyses in Amazon QuickSight and gain valuable insights from your data. If you're looking to enhance your QuickSight dashboards consider integrating them into a no-code internal tool builder for even more customization and control.
