Count customers by condtion

[joeybird]

Hiya

I wish to count a distinct customerID based on column criteria as a KPI.

Columns are

CustomerID, RepeatCust

1                         R

1                         R

1                         R

2                         N

2                         R

2                         R

3                         R

3                         R

3                         R

4                         R

4                         R

I only wish to count the total number of customers that are new and have repeated an order

N and R only

the answer should only be 1 out of 4 (only interested in customers like ID 2)

Every time I do the set analysis I get 4

please help

[sunny_talwar · Accepted solution]

May be this:

Count(DISTINCT {<CustomerID= p({<RepeatCust = {'N'}>})>} CustomerID)

[joeybird]

Hiya

that answer was very similar just for counting a KPI, but need to make sure it counts the correct Customer ID

Reason because I have a chart. That tracks change

over time dimension (There is another column called Date )

CustomerID, RepeatCust  Change

  1                       R                1

  1                       R                1

1                        R                4

2                         N                0

2                         R                2

2                         R                6

3                         R                2

3                         R                2

3                         R                2

4                         R                1

4                         R                1

I only wish to concentrate on customers only like customerID  2

Please help

[sunny_talwar]

To know which customer it is counting, you can use this:

Concat(DISTINCT {<CustomerID= p({<RepeatCust = {'N'}>})>} CustomerID, ', ')

[#5]

you could add an extra load in, e.g.

LOAD

     CustomerID,

     COUNT(DISTINCT RepeatCust) AS Applicable_Cust

RESIDENT [YourSourceTable]

WHERE RepeatCust = 'R' OR RepeatCust = 'N';

then you do set analysis on [Applicable_Cost] = 2

or change the count into an IF, changing it into an indicator:

IF(COUNT(DISTINCT RepeatCust) = 2,'Yes','No') AS Applicable_Cust

[joeybird]

awesome team thank you x