Count if

[#1]

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

[#2]

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

[#3]

Hi,

Thanks for your attention,

Follow attached is the Excel Spreadsheet and the QlikView file.

Bye;

[vincent_ardiet]

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

[#5]

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

[vincent_ardiet]

Oups, a bracket was deleted in the previous one :

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

[#7]

Hello,

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

[vincent_ardiet · Accepted solution]

I have attached your QVW with the corrected formula.

Regards,

Vincent

[#9]

Thanks Vicent,

Now it's working perfectly.

Would you mind to help me with some others doubts?

Thanks, have a nice day!

[#10]

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.