topic Count if in QlikView

Count if

Mon, 18 Apr 2011 13:41:00 GMT

Dears,

It's my first time using QlikView and I'm facing with some doubts.
I would like to create an expression to calculate the NPS (Net Promoter Score). Basically the NPS formula is:

(% of Promoters) - (% of Detractors)

Promoters are those customers who gave us a grate of 9 or 10.
Detractors are those customers who gave us a grate of 1 to 6.

If the customers gave us a grate of 7 or 8 he is considerated Neutral.
But, since it is the (% of Promoters) - (% of Detractos), the total number os respondents consider Neutrals. So I created the follow expression.

=((Count({$<[NPS] = {">=9"}>} [NPS]))-(Count({$<[NPS] = {"<=6"}>} [NPS])))/count([NPS])

It's working good, but if I sort by NPS, choosing a gread of 5 in a ListBox for example, an NPS of 0% is spected, but the QlikView is showing 330%.

Could you please help me to create a better expression?

Thanks for your attention
Best Regards

Kelly Hellen

Count if

Mon, 18 Apr 2011 13:49:02 GMT

can you provide some data's in excel file and give me the current qvw file

Count if

Mon, 18 Apr 2011 15:17:26 GMT

Hi,

Thanks for your attention,

Follow attached is the Excel Spreadsheet and the QlikView file.

Bye;

Re. :Re: Count if

vincent_ardiet — Mon, 18 Apr 2011 15:33:18 GMT

Hi,

I think you have to compute the intersection between the current NPS selection and your inequality.

So your fomula will be :

=((Count({$<[NPS] = [NPS] * {">=9"}>} [NPS]))-(Count({$<[NPS] = [NPS] * {"<=6"}>} NPS])))/count({1<[NPS=]>}[NPS])

Also, I have add the {1<[NPS=]>} modifier in the denominator in order to have constant ratio.

Is it what you want to obtain ?

Regards,

Vincent

Re. :Re: Count if

Mon, 18 Apr 2011 15:51:39 GMT

Hello Vicent,

Thanks for your attention, but this expression is not working.
When I pasted your formula, I got an error: "Error in set modifier expression" and after had pressed Apply, the gauge didn't show anything. Null Value independing of what is selected.

Do you know what is going on?

Thanks

Re. :Re: Re. :Re: Count if

vincent_ardiet — Mon, 18 Apr 2011 15:54:39 GMT

Oups, a bracket was deleted in the previous one :

=((Count({$<[NPS] = [NPS] * {">=9"}>} [NPS]))-(Count({$<[NPS] = [NPS] * {"<=6"}>} [NPS])))/count({1<[NPS=]>}[NPS])

Re. :Re: Re. :Re: Count if

Mon, 18 Apr 2011 16:13:24 GMT

Hello,

It's still not working,
and I've already considerated the bracket in the previous one. I got the same error right now.

Re. :Count if

vincent_ardiet — Mon, 18 Apr 2011 16:23:30 GMT

I have attached your QVW with the corrected formula.

Regards,

Vincent

Re. :Count if

Mon, 18 Apr 2011 16:32:10 GMT

Thanks Vicent,

Now it's working perfectly.

Would you mind to help me with some others doubts?

Thanks, have a nice day!

Re. :Count if

Mon, 18 Apr 2011 16:47:35 GMT

Now I want to create a chart (like a bar graph) with the average NPS by quarter, like:

(First Quarter) - NPS: 50%
(Secound Quarter) - NPS: 10%
(Third Quarter) - NPS: 20%
(Fourth Quarter) - NPS: 30%

In the excel file I have the dates like (01/05/2011) - (MM/DD/YYY)

How can I consolidate and get the average by quarter? And show it in a chart?

Thanks again Vicent.