topic Re: Count if the result of count is less than a number in QlikView

Count if the result of count is less than a number

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

myData:

LOAD * INLINE [Country, Locaton, Test

C1,L1,T1

C1,L1,T2

C1,L2,T3

C1,L2,T4

C1,L3,T5

C1,L3,T6

C1,L3,T7

C2,L4,T8

C2,L4,T9

C3,L5,T10

C3,L5,T11

C3,L5,T12

];

The test ids are unique

A location might have multiple test

A country might have multiple locations

I need to count by country number of test

where count of tests per location is less than 3

for the above data my result should look like this:

Country	Count
C1	4
C2	2
C3	0

thank you all

Re: Count if the result of count is less than a number

Anil_Babu_Samineni — Mon, 21 Aug 2017 15:24:43 GMT

May be this?

Sum(Aggr(Count({<Locaton = {"=Count(Test) < 3"}>}Test), Country))

Please suppress when value is null from presentation tab

Re: Count if the result of count is less than a number

sunny_talwar — Mon, 21 Aug 2017 15:58:23 GMT

I think Anil, this should be enough

Count({<Locaton = {"=Count(Test) < 3"}>}Test)

Assuming a location cannot be shared by two countries... for example L1 location is in country C1 and C2

Re: Count if the result of count is less than a number

Anil_Babu_Samineni — Mon, 21 Aug 2017 16:02:16 GMT

I though too, But i am grouping because they may have multiple location for each countries.

Re: Count if the result of count is less than a number

sunny_talwar — Mon, 21 Aug 2017 16:04:37 GMT

AFAIK the two expression should always give the same result

Re: Count if the result of count is less than a number

Anil_Babu_Samineni — Mon, 21 Aug 2017 16:05:26 GMT

Haha, Yes. That is where i did. Thanks again

Re: Count if the result of count is less than a number

effinty2112 — Mon, 21 Aug 2017 16:32:35 GMT

Hi Boghos, Sunny, Anil,

I'll be the joker in the pack and suggest

sum(aggr(if(count(Test)<3,count(Test)),Locaton))

This has one thing in it's favour - it will return a zero value in a straight table if suppress zero values is unchecked.

Country	sum(aggr(if(count(Test)<3,count(Test)),Locaton))
C1	4
C2	2
C3	0

vs.

Country	count({<Locaton ={"=count(Test)<3"}>}Test)
C1	4
C2	2

If it's not important to show the zero value then the suggestions of Anil and Sunny are better than this.

cheers

Andrew

Re: Count if the result of count is less than a number

antoniotiman — Mon, 21 Aug 2017 16:34:39 GMT

Sunny,

Expression without Aggr() doesn't show C3 = 0.

Re: Count if the result of count is less than a number

sunny_talwar — Mon, 21 Aug 2017 16:39:46 GMT

Got it, I didn't see because I had two expression... might be easy to use this

Count({<Locaton = {"=Count(Test) < 3"}>}Test) + Avg({1} 0)

instead of Aggr()

Re: Count if the result of count is less than a number

Kushal_Chawda — Mon, 21 Aug 2017 17:11:35 GMT

with little scripting you can make the expression more simpler and easy to use with other set modifiers

myData:

LOAD * INLINE [Country, Locaton, Test

C1,L1,T1

C1,L1,T2

C1,L2,T3

C1,L2,T4

C1,L3,T5

C1,L3,T6

C1,L3,T7

C2,L4,T8

C2,L4,T9

C3,L5,T10

C3,L5,T11

C3,L5,T12

];

Left Join(myData)

LOAD Locaton,

Count(Test) as CountTest

Resident myData

Group by Locaton;

Now use below expression

=Count({<CountTest={'<3'}>}Test)

Note:

Uncheck suppress zero option in chart if you want to show zero value