topic Re: Count unique value based on min and max on another column in QlikView

Count unique value based on min and max on another column

edzreeayub — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Count unique value based on min and max on another column

sunny_talwar — Tue, 21 Mar 2017 15:30:46 GMT

May be try this for P-P

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

Re: Count unique value based on min and max on another column

Anil_Babu_Samineni — Tue, 21 Mar 2017 15:35:23 GMT

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)

Re: Count unique value based on min and max on another column

Anil_Babu_Samineni — Tue, 21 Mar 2017 15:36:39 GMT

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

Re: Count unique value based on min and max on another column

sunny_talwar — Tue, 21 Mar 2017 15:38:02 GMT

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)

Re: Count unique value based on min and max on another column

edzreeayub — Wed, 22 Mar 2017 02:13:32 GMT

Thanks Sunny. This works.