topic Re: aggr function in QlikView

aggr function

dasaradh — Thu, 06 Aug 2015 17:33:09 GMT

i have data like this

LOAD * INLINE [

cust no, acc no, bal

1, 1a, 15000

2, 2a, 50000

3, 3a, 20000

4, 4a, 20000

1, 1b, 12000

2, 2b, 5000

5, 5a, 60000

];

i want to count the no of customers who are having bal more than 50000, here i want to group on custno. i want to use in expression level

i have tried this exp but it is not working

=count(aggr(sum(bal)<=50000,[cust no]))

Re: aggr function

maxgro — Thu, 06 Aug 2015 22:19:21 GMT

in a textbox, for less then 50000 (replace with >= for ...)

=sum(aggr(if(sum(bal)<50000,1,0), [cust no]))

Re: aggr function

sunny_talwar — Fri, 07 Aug 2015 01:55:14 GMT

This might also work:

=Sum(Aggr(Count({<[cust no] = {"=Sum(bal) < 50000"}>}[cust no]), [cust no]))

Re: aggr function

dasaradh — Fri, 07 Aug 2015 10:45:37 GMT

its working fine.

but i want to make it more globalize here is my exp

sum(aggr(if(sum(bal)>50000,1,0),customernbr))

here i want pass bal and 50000 as parameters in exp so that i want to build set analysis on this exp how to do that.

am i fallowing correct approach or not i am getting doubt please help in this

thanks in advance@

Re: aggr function

dasaradh — Fri, 07 Aug 2015 10:45:58 GMT

thanks bro

Re: aggr function

sunny_talwar — Fri, 07 Aug 2015 12:54:39 GMT

No problem

I am glad it worked for you.

Best,

Sunny

Re: aggr function

sunny_talwar — Fri, 07 Aug 2015 12:56:37 GMT

Try this:

=Sum(Aggr(Count({<[cust no] = {"=Sum(bal) < $(vVar)"}>}[cust no]), [cust no]))

Where vVar is set using a inputbox object or slider object or any kind of variable input object

HTH

Best,

Sunny

Re: aggr function

dasaradh — Fri, 07 Aug 2015 13:00:52 GMT

i am also feeling the same

i want to make it more globalize here is my exp

sum(aggr(if(sum(bal)>50000,1,0),customernbr))

here i want pass bal and 50000 as parameters in exp so that i want to build set analysis on this exp how to do that.

am i fallowing correct approach or not i am getting doubt please help in this

thanks in advance@

Re: aggr function

dasaradh — Fri, 07 Aug 2015 13:09:24 GMT

here bal is also vary i mean some times it is BalnaceEur,BalanceDoller,LocalCurrunacy like that, that y i want to make it as variable

Re: aggr function

dasaradh — Fri, 07 Aug 2015 13:11:19 GMT

and also we using it as expression not in text object

Re: aggr function

sunny_talwar — Fri, 07 Aug 2015 13:15:01 GMT

Use this:

=Sum(Aggr(Count({<[cust no] = {"=Sum($(vVar1)) < $(vVar)"}>}[cust no]), [cust no]))

Where you control vVar1 for different Balances

If(somethingsisTrue, BalanceEUR, BalanceUSD)

Re: aggr function

sunny_talwar — Fri, 07 Aug 2015 13:16:42 GMT

The expression should work as a chart expression as well. Only thing is that if you are using this in a chart where there is only one dimension and that dimension is [cust no], you can simplify the expression to this:

Count({<[cust no] = {"=Sum($(vVar1)) < $(vVar)"}>}[cust no])

HTH

Best,

Sunny

Re: aggr function

dasaradh — Mon, 10 Aug 2015 11:15:30 GMT

hi sunny,

could you please check this

Set analysis in expression