I am trying to calculate the formula:
sum(count) * avg(var1)
When I implement the above formula, I get the error, Nesting of aggregate functions is not allowed
var(1) itself is a calculated field with the formula:
var1 = [sum(coalesce({var3},0)*coalesce({var4},0))/sum({var4})] - [sum(coalesce({var5},0)*coalesce({var6},0))/sum({var6})]
I want to avg var1 over all the rows where it is non null, and then multiply by the sum of count for that grouping how to do this?
Hello @preyasi, I have a few questions about how you are wanting this to aggregate. There is definitely a way to manage nesting of calculations like this, but I think we can simplify this slightly and avoid the errors. Something that would make this a lot easier to debug would be if you could create and share a demo version of this analysis within QuickSight Arena. That would allow me to test and share a working version of the calculation with you directly.
As for the calculation, is var(1) a field that you want aggregated at a row level? Comparing these values across a single row then getting the average across all rows? As for the count field, it is probably unnecessary to wrap it in a sum aggregation. You should be able to multiply the count by the average.
My initial thought is to update the var(1) aggregation to look like this:
var(1) = (({var3}*{var4})/{var4})/(({var5}*{var6})/{var6})
Now for the count for that grouping, do you just mean the count of rows that do not return a NULL value for var(1)? You shouldn’t have to do anything special for that. Your final formula should look something like this:
count({var(1)})*avg({var(1)})
Let me know if this helps!
Hello @preyasi, did my previous response help you resolve the issue you are facing in QuickSight? I will mark it as the solution, but please let me know if you have any remaining questions and I can help guide you further. Thank you!