topic Re: Using aggr with if statement in QlikView

Using aggr with if statement

Thu, 30 Apr 2015 20:19:49 GMT

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

Re: Using aggr with if statement

sunny_talwar — Thu, 30 Apr 2015 20:25:32 GMT

What is the expected output?

Re: Using aggr with if statement

Thu, 30 Apr 2015 20:27:36 GMT

F1| Expr

a | 4.555

b | 5

Re: Using aggr with if statement

sunny_talwar — Thu, 30 Apr 2015 20:29:44 GMT

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

Re: Using aggr with if statement

sunny_talwar — Thu, 30 Apr 2015 20:31:23 GMT

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

Re: Using aggr with if statement

Thu, 30 Apr 2015 20:38:13 GMT

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,

Re: Using aggr with if statement

sunny_talwar — Thu, 30 Apr 2015 20:43:15 GMT

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