Percentile Rank

I’m trying to replicate Tableau’s percentile rank function in Quicksight

Tableau Calculation:
IF ATTR([Responses above Threshold]) THEN RANK_PERCENTILE(AVG([Resps Pct W] ))
ELSE NULL END

Quicksight Calculation:
ifelse(
{Responses above Threshold} = 1, percentileRank({resps_pct_w})
, NULL
)

Error: Expression percentileRank({resps_pct_w}) for function percentileRank has incorrect argument type percentileRank(Decimal). Function syntax expects Non-empty List, List, Calculation Level
Non-empty List, List
‘percentileRank()’.

Also tried with square brackets,
percentileRank([{resps_pct_w}])

Gives error Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

{resps_pct_w} is a measure
{Responses above Threshold} is a T/F field… I checked in Quicksight it shows value as 1 or 0.

Anyone know what changes would I need in my calculation to make it work in quicksight?

Hello @jemin, I think you might be having an issue because you are not including an aggregation for {resps_pct_w} within your function and you are missing an ASC or DESC selection. I would try creating the calculated field with the ifelse statement to return {resps_pct_w} or NULL.

Then create another calculated field that will create your percentile rank using the calculated field you made with the ifelse. It would look like this:

percentileRank([avg({calculatedField}) DESC])

Let me know if that helps! I’ll include the documentation as well for the function in case you want to dig into it some more.

Hi @DylanM ,

Thank you for your guidance, I tried including aggregation in the calculations but it gives aggregation error:
Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.
percentileRank([avg({resps_pct_w}) DESC])

Hello @jemin, did you try creating the ifelse statement to return {resps_pct_w} based on your conditions, and then pass that value into a new calculated field with the percentileRank function? Make sure you return just the value of {resps_pct_w} in the ifelse without a function like sum or avg.

1 Like

Hello @DylanM ,

Thank you again for your kind response. I tried the other approach you mentioned, that works and I don’t face any errors but it doesn’t calculate the percentile rank correctly.
I checked the row level data matches between Tableau & Quicksight but percentile rank differs.

One thing I noticed is the percentile rank calc field is a table calculation in tableau and is set to calculate across multiple fields which I think is not happening in QS. Any suggestions around that?

Tableau’s calculated field using percentile rank calculated field:
IF [tc_rank pct resps pct] >= 0.8 THEN “5 - High”
ELSEIF [tc_rank pct resps pct] < 0.8 AND [tc_rank pct resps pct] >= 0.6 THEN “4 - Medium High”
ELSEIF [tc_rank pct resps pct] < 0.6 AND [tc_rank pct resps pct] >= 0.4 THEN “3 - Medium”
ELSEIF [tc_rank pct resps pct] < 0.4 AND [tc_rank pct resps pct] >= 0.2 THEN “2 - Medium Low”
ELSEIF [tc_rank pct resps pct] < 0.2 THEN “1 - Low”
ELSEIF ISNULL([tc_rank pct resps pct]) THEN “0 - NULL”
END

Hello @jemin, out of curiousity what type of datasource are you utilizing to bring your data into QuickSight, RDS, Snowflake, etc? I am wondering if utilizing a percentile function within your SQL when pulling your data into a dataset might lead better results for this. I could craft a possible solution based on your datasource functionality.

Hi @DylanM ,

I’m using Redshift as the underlying datasource/warehouse in quicksight.

Redshifts tables → custom sql to left join those tables in quicksight → qs analysis calculated fields for percentile

Hello @jemin, so I have done something similar to this myself. I had to build out decile groupings and I did it through the SQL. It looks like you are doing groupings of 6, so you could utilize an NTILE Over function that is built for Redshift like this ntile(6) over(order by pricepaid desc) from sales (this is just the example data fields).

That wouldn’t build groupings based on exact percentiles, but it would ensure that each group has the exact same number of users or groups that are being compared.

A 2nd option would be using the percentile_cont function for each of the 6 percentages you would want to display. That would require a bit more work, but I’ve done it before. It can slow down your query a bit so you need to keep an eye on that.

Appreciate your response @DylanM ,
that is a great suggestion! Unfortunately, the percentile rank on my end is dependent on some parameters and calculated fields output.
I can move over the calculations to sql but can’t do parameters, so doing ntile in sql might not be the route to go.

Hello @jemin, what you will probably need to do then is create a calculated field utilizing the percentileCont() in QuickSight for each of the percentiles you want to display. You can test it out with you 20%, 40%, 60%, and 80% percentiles first and see if that gets you closer to your expected solution!

Hi @DylanM ,

I’m almost there with a solution for this. I’m facing one challenge though if you could guide with that:

I’ve a calculation that is calculating table across but I want it to calculate table down. I’m not using that calc in the view but as a field for conditional formatting.

percentileRank([avg({resps_pct_w}) ASC],[{talent_name}, {wve_date}])

For QA I included resps_pct_w in the view and did add table calculation percentile and table across matched with the above calculation values.

Hello @jemin, when you say it is calculating the table across what do you mean? I know you had mentioned utilizing conditional formatting to me, did you set that to “Entire Row” instead of a single column?

Hello @DylanM

in the screenshot below - the colored 83.33% is when I selected table across.
and it matches with the percentile rank value of 83.33 which I get from the calculated field…

percentileRank([avg({resps_pct_w}) ASC],[{talent_name}, {wve_date}])

That’s how I know the calc field is calculating across the row instead of column…

Hi @DylanM ,

I think I got it… I switched the partition by fields in my calculated field from rows to column fields and that gives the same values as table down.

Really Really Appreciate your help here!

1 Like

Hello @jemin, I am glad you figured it out! Good luck finishing up your dashboard!