Count unique value based on min and max on another column

[edzreeayub]

Hi,

I want to do an analysis on my Customer NPS Trend, I wonder if it's possible to count how many customer which follow this trend based on min date and max date. For example:

Promoter to Promoter trend:

Based the first date (3/1/2017) the cb_score is 1 and I compare with the last date (3/11/2017). I want to count unique cb_telno.

Another example is Promoter to Detractor.

Based the first date (3/6/2017) the cb_score is 1 and I compare with the last date (3/8/2017) which the cb_score is -1.

Here's how the whole data look like:

And I want the final result to look like this:

I only count for cb_telno which have more than 1 row.

So far I've tried to use this expression for Promoter to Promoter but it seems the min max Date would not work.

Count(DISTINCT {$<Date = {"$(=min(Date))"},cb_score = {'1'}> *

$<Date = {"$(=max(Date))"},cb_score = {'1'}>} cb_telno)

I would greatly appreciate any help that anyone can give me in solving this.

[sunny_talwar]

May be try this for P-P

Count(DISTINCT {$<cb_telno = {"=FirstSortedValue(cb_score, Date) = 1 and FirstSortedValue(cb_score, -Date) = 1"}>} cb_telno)

[Anil_Babu_Samineni]

May be this?

Edzree Ayub wrote:

Hi,

I want to do an analysis on my Customer NPS Trend, I wonder if it's possible to count how many customer which follow this trend based on min date and max date. For example:

Promoter to Promoter trend:

Based the first date (3/1/2017) the cb_score is 1 and I compare with the last date (3/11/2017). I want to count unique cb_telno.

Another example is Promoter to Detractor.

Count({<_%wscKey = {'$(=Max(_%wscKey))'}>}Distinct cb_telno)

[Anil_Babu_Samineni]

Or

Count({<_%wscKey = {'$(=Max(SubField(_%wscKey,-1)))'}>}Distinct cb_telno)

[sunny_talwar · Accepted solution]

Or may be add the Count() condition also

Count(DISTINCT {$<cb_telno = {"=FirstSortedValue(cb_score, Date) = 1 and FirstSortedValue(cb_score, -Date) = 1 and Count(cb_telno) > 1"}>} cb_telno)

[edzreeayub]

Thanks Sunny. This works.