Aggr() Count help

[#1]

Hi all,

I'm working with telecommunication/wireless telephone data, and I want to count the number of "Inactive" lines in a text box. What I've done so far is aggr() each phone line ("PhoneLineNumber" below) by the sum of their data + voice usage. Now what I want to do is count the numbers whenever that Aggr() expression evaluates to zero. How do I count those? Set analysis? If statement?

aggr(sum(UsageDataKBTotal) + sum(UsageTotalVoiceMOU), PhoneLineNumber)

Thanks!

[swuehl · Accepted solution]

=sum( aggr(if(sum(UsageDataKBTotal) + sum(UsageTotalVoiceMOU)=0,1), PhoneLineNumber) )

[MK_QSL]

Didn't understand your question properly. Could you please elaborate little more?

[#4]

Thanks! Great use of sum rather than count().

-Paul

[#5]

Swuehl,

What if I only wanted to do the same as above, but only count the phone numbers of a certain length - such as where len(PhoneLineNumber) = 7? Is that possible?

Thanks

[swuehl]

Sure, maybe like

=count( if(len(PhoneLineNumber)=7,PhoneLineNumber))

or a distinct count if needed:

=count(DISTINCT if(len(PhoneLineNumber)=7,PhoneLineNumber))

?

Or prepare a field PhoneLineNumberLength in your script:

LOAD

     PhoneLineNumber,

     len(PhoneLineNumber) as PhoneLineNumberLength,

     ...

then

=count(DISTINCT {<PhoneLineNumberLength = {7}>} PhoneLineNumber)

[#7]

I mean inside the formula you provided above. Something like (although this doesn't work):

=sum( if(aggr(sum(UsageDataKBTotal) + sum(UsageTotalVoiceMOU), if(Len(PhoneLineNumber) = 12, PhoneLineNumber)) = 0, 1))

[#8]

I actually got it! Thanks for the help again:

=sum( if(Len(PhoneLineNumber) = 12 AND aggr(sum(UsageDataKBTotal) + sum(UsageTotalVoiceMOU), PhoneLineNumber) = 0, 1))