I’m trying to fetch the previous row calculation (this is a separate calculated field which totals all the column values) dynamically in the current row so that balance can be brought forward for next months. Please see a similar example in below table.
Please note that the previous row value fetched is not a pre-computed value from source but a calculated field in QS itself. I’m getting below error message when trying to create a calculated field for first column using this formula - ifelse(month = 1, sum(beginning_bal), lag(sum(ending_balance), [month ASC], 1,)
Error message - Nested aggregations are not allowed.
It’s treating lag(sum(ending_balance)) as a nested aggregation while I see examples of such calculations being done even on Quicksight lag() function official documentation.
Has anyone encountered this scenario before? How to solve this?
It sounds like your ‘ending balance’ column is the ‘previous row value’ that’s a calculated field, correct?
If so, could you please share the syntax for that calculated field?
Additionally, this may be easier to assist further if you’re able to upload a copy of your analysis with annonymized data to QuickSight Arena so that I can test out some various scenarios:
Hi @Brett , thanks for your response. Yes, the ending balance in my sheet is also a calculated field which sums up all the different components of the expense.
However, I was able to solve this by using runningSum() in the ending_balance calculated field instead of just sum(). So, I could bring up the previous row ending balance using lag(runningSum(…)) in a seperate calculated field. Hence, the take away is lag(runningSum()) works while lag(sum()) gives nested aggregation not allowed error.
