Count based on a condition where the condition is a calculation

[#1]

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)

[#2]

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.

[#3]

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)

[m_woolf]

try it with DISTINCT spelled correctly

[#5]

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