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.
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.
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?
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.
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!
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.
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?
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…
