topic Re: Count based on a condition where the condition is a calculation in QlikView

Count based on a condition where the condition is a calculation

Fri, 22 Jun 2012 21:05:42 GMT

I'm trying to count the number of recently added companies to our db based on two conditions, where one condition is a calculation.

1) The first condition is the customer associated with the company must be a "new" customer. Hence, I created a field through scripting called NEW_CUSTOMER that designates customers added within the past 30 days.

2) The second condition, where I'm experiencing problems, is trying to identify companies where the count of companies =1, indicating the first instance of a given company in the db.

Hence, the first condition identifies companies with brand new customers, and the second condition identifies companies where no other instance of a given company exists in the db. Together, they should produce a list of "new" companies.

I have the following expression as my starting point. The result tells me that the NEW_CUSTOMER portion of the expression is working, but the count of companies in the second condition is not working. Could anyone help with a solution please?

Count ({<NEW_CUSTOMER={'New'} AND Count(CONTACT_COMPANY)='1'>} distinct CONTACT_COMPANY)

Re: Count based on a condition where the condition is a calculation

Sat, 23 Jun 2012 07:23:05 GMT

Hi Mattheb,

Try this one:

Count({$< NEW_CUSTOMER={'New'} ,CONTACT_COMPANY={"=Count(DISTINC CONTACT_COMPANY=1"} >} CONTACT_COMPANY)

Hope i understend your needs,

Regars,

Yigal.

Re: Count based on a condition where the condition is a calculation

Mon, 25 Jun 2012 17:20:56 GMT

Yigal, thanks for responding. I tried your suggestion (shown below), but it generates an empty pivot table. I've tried adding Contact_Company as a dimension, so I could at least see the list of companies, but no results appear.

Count({$< NEW_CUSTOMER={'New'} ,CONTACT_COMPANY={"=Count(DISTINC CONTACT_COMPANY=1"} >} CONTACT_COMPANY)

Re: Count based on a condition where the condition is a calculation

m_woolf — Mon, 25 Jun 2012 17:50:21 GMT

try it with DISTINCT spelled correctly

Re: Count based on a condition where the condition is a calculation

Mon, 25 Jun 2012 18:03:22 GMT

I did make the correction before trying, and I removed a few of the unnecessary spaces within the expression.