topic Re: Count if (with aggr()) in QlikView

Count if (with aggr())

Wed, 16 Jan 2013 15:10:24 GMT

Hello,

I have a list of transactions (multiple for each widget) and I'm trying to count the number of widgets for which the number of workdays between A and B is > 252. The example below would produce a count of 1 because widget_num 120 is > 252 working days.

widget_num A B

120 Jan 1/08 Jan 4/10

121 Feb 1/08 Feb 4/08

My expression below isn't working:

count({<aggr(max(NetWorkDays(A,B)),widget_num)={">252" }>} widget_num)

Help!

Thanks,

Daneen

Re: Count if (with aggr())

danielrozental — Wed, 16 Jan 2013 15:13:46 GMT

Try something like count(if(NetWorkDays(A,B)>252,widget_num))

Re: Count if (with aggr())

erichshiino — Wed, 16 Jan 2013 17:38:08 GMT

Hi,

Aggr() can be a very expensive function ( it can take a lot of time and memory)

I suggest you create another field in script.

Load A,

NetWorkDays(A,B) as NetworkdaysAB,

widget_num

from / Resident...

Then, the expression becomes:

count ( { <NetworkdaysAB={">252"} >} widget_num )

Hope it helps,

Erich

Re: Count if (with aggr())

Wed, 16 Jan 2013 19:08:45 GMT

Thank you Daniel. A variation on this ended up working for me.

count(if(aggr(max(NetWorkDays(DateTime_Reported_To_Ceding_Company,DateTime_Reported_To_FMRP)),CLAIM_ID)>252,CLAIM_ID))

Erich, I'm just a front end user - I don't think I have the capability to work with the script but thank you for the advice on efficiency.