Conditional Summary functions

Hi,
I’m trying to create a calculated column that’ll summarize ratios depending on the conditions (‘ABC’,‘XYZ’…). When I attempt to do so, without using a sum function the formula executes with no error, but the values are not correct.
Looks like I need to add a sum function to get the correct output but doing so throws an error -

“custom aggregations can’t contain both aggregated and nonaggregated fields in any combination”
Here is my query-

ifelse(CATEGORY=‘ABC’ OR CATEGORY=‘CID’,sum({a1})/sum({b}),
ifelse(CATEGORY=‘XYZ’,sum({a2})/sum({b}),
ifelse(CATEGORY=‘LMN’,sum({a3})/sum({b}),
ifelse(CATEGORY=‘OPQ’,sum({a4})/sum({b}),NULL)
)
)
)
Any help would be appreciated

Try taking the sum out of the return statement.

Something like this.

sum(ifelse(CATEGORY=‘ABC’ OR CATEGORY=‘CID’,{a1},CATEGORY=‘XYZ’,{a2},CATEGORY=‘LMN’,{a3},CATEGORY=‘OPQ’,{a4},NULL)/sum({b})

Let me know if that works!

1 Like

The idea works perfectly, though it did throw some errors. Here’s the corrected query -
sum(ifelse(CATEGORY=‘ABC’ OR CATEGORY=‘CID’,{A1},
ifelse(CATEGORY=‘XYZ’,{A2},
ifelse(CATEGORY=‘LMN’,{A3},
ifelse(CATEGORY=‘OPQ’,{A4},NULL)
)
)
))/sum({B})

Thanks for your input!

1 Like