Community Conundrums are live! Learn more

Percentiles on a column

Level 2
Percentiles on a column

I would like to group a large data set by column (A) and calculate Percetiles on a separate numeric column (B).

I can only see the median, max, min, etc in grouping recipe. any idea?

0 Kudos
2 Replies
Dataiker
Dataiker

you can achieve this by using a Window recipe, where the window partitions on A, orders by B, has a frame where no limit is set (ie no limit on preceding, no limit on following) and you ask for 100 quantiles.

Level 2
Author

thanks, It worked,

with the above, i get rather than the percentile value the percentile bucket for each original record which is halfway

then I have to add another step and group by the same Column A plus the newly created percentile (quantile) buckets and select the MAx value of the Column B  to get to the value of each percentile...

eventually, i get the percentile values

 

If i use 4 ntiles  i take Max value of  ntlie 3 to get the value if the 3rd Quartile

@fchataigner2 

0 Kudos