CODEWITHSUNDEEP
if-statementtableau-apicalculated-columns
AS91
AS91

Reputation: 527

IF calculation with aggregate functions

A   B      UA
1   0    Negative
1   1    Negative
1   1    Positive
2   5    Negative
2   2    Positive

I want to calculate %UA Negative such that when A = B then count all the Negatives in the UA column and divide that by total number of results where A = B. So for A=1 and B=1, %UA Negative = 1/2 = 0.5

I tried:

 IF [A] = [B] THEN
     SUM(IF[UA] = 'Negative' THEN 1 ElSE 0 END)/COUNT([UA]) END

but I'm getting the error:

Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions

Upvotes: 0

Views: 6575

Answers (1)

Bernardo
Bernardo

Reputation: 3318

You can either place the first IF statement inside your Sum and Count aggs or place ATTR around the first IF statement. 

SUM(IF [A] = [B] THEN IF[UA] = 'Negative' THEN 1 ElSE 0 END END)/COUNT(IF [A] = [B] THEN [UA] end)

or 

IF ATTR([A]) = ATTR([B]) THEN
 SUM(IF[UA] = 'Negative' THEN 1 ElSE 0 END)/COUNT([UA]) END

The latter converts your first IF statement to an agg.

Upvotes: 3

Related Questions