Percentile functionality

Question & Answer
, ,
1

Hi folks,

I am trying to get the top 0.90 percentile for NPS score and participatoin of some departments. The data is as such
image
Now, in Jupiter notebook, I am able to write the code for percentile for both, as shown below
image
But since, in Quicksight, nesting of aggregated function is not allowed, I am not able to arrive at the same scores.
For your reference, NPS value is %Promoters - %Detractors
Can someone guide me as to how this number can be achieved?

2

Can you do a percentile?

percentileDisc( {nps} , 90)

3

Hi @Max , so like I mentioned, the enps is an aggregated column; a calculated field - where we calculate %Promoters - %Detractors. To this, I am getting the error of nesting of aggregated function is not allowed. So need a work around for this.

4

What is that calculated field?

5

nps is the calculated field

6

How is that calculated? Can you give me the formula?

7

%promoters - %detractors
(distinct_countIf({Participants},{promoter}=1)-distinct_countIf({Participants},{detractor}=1))/distinct_count({Participants})

8

Can you try and do this with a distinctCountOver with an ifelse instead of distct_Countif.

percentileDisc(distinctCountOver(ifelse({queue_Name}=‘WLS’,{contact_queued},NULL),[],PRE_AGG),90)

You will need to replace the bold part with your nps score with distinctCountOvers and ifelses.

Let me know if that helps.

1 Like