How to use nested aggregate functions?

I need to count an ID if the sum of other fields for that ID are both > 0.

I can easily do this in a table by making a table grouped by ID, and filtering for field A and field B to be > 0. Then I get a total at the bottom of the table of the number of IDs there.

However, if I try to do the equivalent logic in a calculated field: countIf(ID, sum(field A) > 0 and sum(field B) > 0), I get an error that I can’t nest aggregate functions.

How can I make a calculated field with the same behavior as the table totals?

1 Like

Hello @oconnellpatrickc, my first thought would be to utilize an ifelse statement rather than a countIf statement to accomplish this. I think it would provide a work-around for the aggregation error. Since you are aggregating Field A and Field B in your statement, it requires any other field you are using to be aggregated as well. Give this a shot and let me know if it resolves the issue:

ifelse(
sum(field A) > 0 AND sum(field B) > 0, count({ID}),
NULL
)

That should remove the aggregation issue. Let me know if this gives you the result you are looking for!

Hi @DylanM, this does not work.

This logic seems to check if the overall sum of field A > 0 and the overall sum of field B > 0, and if those are both > 0, it counts every ID (even if for a particular ID the sum if field A and sum of field B are both 0).

I need it to only count the IDs for which the sum(field A) > 0 and sum(field B) > 0.

1 Like

Hello @oconnellpatrickc, it might be beneficial to put a demo version of your analysis within Arena so I can test this in the actual QuickSight console. Basically, we either need to figure out a way to aggregate the ID field or remove aggregations from field A and field B to make this calculated field work.

In your table, what partition are you summing field A and B by? If it is in a table with those partitions, it may not be necessary to use sum within the calculated field at all and you might be able to check if {field A} > 0 AND {field B} > 0 and return {ID}, then run a count on the result. With a little more information about your table, I should be able to guide you further.

Hello @oconnellpatrickc, did my last response help guide you towards your expected output? I will mark it as the solution, but please let me know if you still have questions regarding this issue. Thank you!