Quantile of a list

Mazze

Hi everyone,

I have a list of 2500 entries and growing. Now I want to add a night-task that re-evaluates specific entries and give it some sort of a score.

What I now need is to say "the lower 25% of all values are getting an €, the ne 25% - 40% will get an €€ and so on".  

I here need to take the quantile of that values, as I cant just go with "0 - 20% of all entries get an €" as there are some double-entries like serveral times there are prices with 5,99€ and so on.

Thanks for help 🙂

Michael Udinski

 is this list a field on a record in your DB or is the list the 2500 entries in your database (as in 2500 records)?

Mazze

Our database consists of 2500+ records

Mazze

No one an idea? I tried different approaches with sorts and loops but can't figure it out

Sean Montgomery

Hey ,

I believe quantile would be synonymous with unique values.

Therefore you could do a query all records but change the output type to an aggregate. Then you could group by the price field. This would then give you a list of prices that are unique. You also could sort the prices ascending which would mean the lower values start at the beginning.

In terms of getting the 20%... you would take the count of the list of values and multiple that by 20% and then floor it.

var my20value = var items|count|mul(.2)|floor

This would then give you the amount of items you want.

Then you could get them with the slice filter...

var items|slice(0, var my20value)

I believe that would solve your problem. This is a very interesting question and happy to walk you through this in person during office hours.