Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
May be try this for P-P
Count(DISTINCT {$<cb_telno = {"=FirstSortedValue(cb_score, Date) = 1 and FirstSortedValue(cb_score, -Date) = 1"}>} cb_telno)
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)
Or
Count({<_%wscKey = {'$(=Max(SubField(_%wscKey,-1)))'}>}Distinct cb_telno)
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)
Thanks Sunny. This works.