Calculating a Percentage Rank or Percentile

[hannah.paxton]

Hello,

Is there a way I can calculate a percentage rank or a percentile within AQL? I’m currently using a window function to rank rows by a certain metric, but I’m having a hard time getting the max of the rank to complete the percentage rank calculation.

[tan (Tan Huynh)]

In this case, I think you can do something like this

metric rank_asc = rank(order: count_orders);
metric sample_size = window_count(count_orders, ..);
metric percentile = (rank_asc - 1) * 1.0 / (sample_size - 1);

This use the same formula as Excel:

Where:

• Number of values lower than the current value is calculated as:
  
• Sample size is a simple window_count on the full range of data.

You can see this in action here

[hannah.paxton]

Exactly what I was looking for - thank you!