One of the most useful functions in Excel is percentilerank, which calculates the percentile of a value within a range of values. The closest I've been able to get is to do a p10, p20, p30, etc. and then search for everything below the value. I'm trying to show benchmarks and the function would be quite useful.
If you're not familiar with the percentilerank function, it works as follows:
It's basically the converse of the perc commands.
This should be it:
... | stats count by value | sort + value
| streamstats current=f sum(count) as rank | fillnull rank
| eventstats sum(count) as total | eval percentile_rank = rank / total * 100
This will assign each unique value its absolute rank and then convert that to percentile rank using the total number of values.
This should be it:
... | stats count by value | sort + value
| streamstats current=f sum(count) as rank | fillnull rank
| eventstats sum(count) as total | eval percentile_rank = rank / total * 100
This will assign each unique value its absolute rank and then convert that to percentile rank using the total number of values.
Nice - it even accounts for duplicate values!
Have you tried any of these? p<X>(Y) | perc<X>(Y) | exactperc<X>(Y) | upperperc<X>(Y)
http://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/CommonStatsFunctions