Count if (with aggr())

[#1]

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    

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

[danielrozental]

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

[erichshiino]

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,

     B,

     NetWorkDays(A,B) as NetworkdaysAB,

     widget_num    

from / Resident...

Then, the expression becomes:

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

Hope it helps,

Erich

[#4]

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.