Using aggr with if statement

[#1]

Hi,

I have  expressions that are aggr expressions. If i use them individually they give the correct values but if i use an if statement they do not give values. For eg pls see attached qvw. I am trying to use a table with a dimension a and b. For a i want to show aggr(expr1) and for b i want to show aggr(expression2). Can aggr functions not be used with if statements ?

Regards

[sunny_talwar]

What is the expected output?

[#3]

F1| Expr

a  | 4.555

b  | 5

[sunny_talwar]

Partially working:

if(F1='a',sum((tenure*aggr(NODISTINCT count({<flag={1}>}distinct(accounts)),tenure)))/count({<flag={1}>}distinct(accounts))

,if(F1='b',

sum((tenure*aggr(NODISTINCT count({<flag={0}>}distinct(accounts)),tenure)))/count({<flag={0}>}distinct(accounts)),0

))

Need to figure out why they are doubling. Maybe you can figure that out because of a better understanding of the data behind the scene.

Best,

Sunny

[sunny_talwar · Accepted solution]

Here, this is working

if(F1='a',sum(DISTINCT(tenure*aggr(NODISTINCT count({<flag={1}>}distinct(accounts)),tenure)))/count({<flag={1}>}distinct(accounts))

,if(F1='b',

sum(DISTINCT(tenure*aggr(NODISTINCT count({<flag={0}>}distinct(accounts)),tenure)))/count({<flag={0}>}distinct(accounts)),0

))

Best,

Sunny

[#6]

This does appear to be working on the small data set that I have created thank you,but can you please explain the use of the extra distinct and nondistinct so that I can better understand what you did and why you did it?

Thanks for your help

Regards,

[sunny_talwar]

Had to use Distinct after Sum because of your data model. F1 was a island table which was duplicating all the records when getting used in the same table. You can live without that if your original data is not duplicating. The NODISTINCT is telling the Aggr function to repeat the same value for all the value in your table. Otherwise by default Aggr uses Distinct.

HTH

Best,

Sunny