topic Re: Count based on other information in QlikView

Count based on other information

Anonymous — Thu, 09 Jul 2015 16:08:53 GMT

Hi All.

I am trying to count the number of customers if the condition is met, but I don't get the correct answer.

Ex.

Pol_no	info_no	customers
11223	12515	A
1233	11223	B
105053	105053	C

I used:

if(Pol_no = info_no),count(distinct customers))

What else can I try to give me the answer of 2?

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:12:17 GMT

May be this:

=Count({<customers = {"=Pol_no = info_no"}>}DISTINCT customers)

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:14:02 GMT

In your above example, the answer should be 1, right? Only for the last row Pol_no = Info_no?

Re: Count based on other information

Qrishna — Thu, 09 Jul 2015 16:14:23 GMT

if( Pol_no = info_no ,customers)

if 'Customers' column contains the 'number of customers' then y do u want count again?

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:14:57 GMT

Or =Count(DISTINCT If(Pol_no = info_no, customers))

Re: Count based on other information

Qrishna — Thu, 09 Jul 2015 16:16:00 GMT

if you want to count on front end then use Set expression as sunindia‌ 's

Re: Count based on other information

Anonymous — Thu, 09 Jul 2015 16:17:18 GMT

Hi,

maybe you want to have 1 for total result.

Regards.

Re: Count based on other information

stigchel — Thu, 09 Jul 2015 16:21:10 GMT

Try with

=Count({<Pol_no=P(info_no)>} customers)

Re: Count based on other information

Anonymous — Thu, 09 Jul 2015 16:41:08 GMT

Hi. No the answer should be 2. it shouldn't look at the order of the values, if the number is in col 1 and in col 2 it should be counted. thus the answer should be 2

11223 = 11223

105053= 105053

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:43:11 GMT

You have a typo in your sample

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:43:39 GMT

Try this:

=Count({<customers = {"=Pol_no = info_no"}>}DISTINCT customers) or

=Count(DISTINCT If(Pol_no = info_no, customers))

Re: Count based on other information

Anonymous — Thu, 09 Jul 2015 16:44:15 GMT

Because the condition has to be met, so only some of the customers should be counted

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:46:16 GMT

Attaching a sample to look at.

HTH

Best,

Sunny

Re: Count based on other information

stigchel — Thu, 09 Jul 2015 16:50:04 GMT

Have you tried my answer?

=Count({<Pol_no=P(info_no)>} customers)

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:51:52 GMT

I just did Piet‌ and it works like a charm

Re: Count based on other information

Anonymous — Thu, 09 Jul 2015 16:54:00 GMT

Thank you, it workes

Re: Count based on other information

sunny_talwar — Thu, 09 Jul 2015 16:55:41 GMT

No problem