topic Re: Aggr() Count help in QlikView

Aggr() Count help

Mon, 16 Jun 2014 19:42:31 GMT

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!

Re: Aggr() Count help

swuehl — Mon, 16 Jun 2014 19:43:46 GMT

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

Re: Aggr() Count help

MK_QSL — Mon, 16 Jun 2014 19:44:00 GMT

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

Re: Aggr() Count help

Mon, 16 Jun 2014 19:47:52 GMT

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

-Paul

Re: Aggr() Count help

Mon, 16 Jun 2014 20:05:20 GMT

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

Re: Aggr() Count help

swuehl — Mon, 16 Jun 2014 20:26:02 GMT

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)

Re: Aggr() Count help

Mon, 16 Jun 2014 20:41:54 GMT

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))

Re: Aggr() Count help

Mon, 16 Jun 2014 20:45:37 GMT

I actually got it! Thanks for the help again:

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