I am having a category column in my data set which is having only 0,1,2 as its data. But for now, the data in it is only 0,1. But the data set is being updated and there is a chance of getting 2 as well. So There is some calculation that has to be performed including 2 if the 2 is in the data and excluding 2 if it is not in data, so I have the calculations working in different fields but when I try to brief this logic into an if else statement there is mismatched aggregation error. The syntax used is as follows:
ifelse({Category}=2,{a},{b})
Syntax for {a}: ((sumIf({X},{Category} =0 AND {Z}=1))/(sumIf({Y},{Category}=0 AND {Z}=1))(sumIf({Y},{C}=1 AND {Category}=0))/(sumIf({Y},{C}=1))+(sumIf({X}, {Category}=1 AND {Z}=1))/(sumIf({Y},{Category}=1 AND {Z}=1))(sumIf({Y},{C}=1 AND {Category}=1))/(sumIf({Y},{C}=1))-(sum({X}{Z})/sum({Y}{Z})))*100
Syntax for {b} : ((sumIf({X},{Category} =0 AND {Z}=1))/(sumIf({Y},{Category}=0 AND {Z}=1))(sumIf({Y},{C}=1 AND {Category}=0))/(sumIf({Y},{C}=1))+(sumIf({X}, {Category}=1 AND {Z}=1))/(sumIf({Y},{Category}=1 AND {Z}=1))(sumIf({Y},{C}=1 AND {Category}=1))/(sumIf({Y},{C}=1))+(sumIf({X}, {Category}=2 AND {Z}=1))/(sumIf({Y},{Category}=2 AND {Z}=1))(sumIf({Y},{C}=1 AND {Category}=2))/(sumIf({Y},{C}=1))-(sum({X}{Z})/sum({Y}*{Z})))*100
The error is listed below:
Mismatched aggregation. Custom aggregations can’t contain both aggregate “SUM” and non-aggregated fields “SUM(CASE WHEN “014f8862-7b66-4d6d-b8b3-5108b28bce9b” = _UTF16’Missing’ AND “db0325c2-3781-4d84-b01a-5facae1dc572” = 1 THEN “2c1d4377-062d-4188-ae7f-e154542882d3.X” ELSE NULL END)”, in any combination.
Kindly help.