Archive

How do you find the top 5% of data in a table?

New Member

I have a table that contains hours worked against each task.

Now i want to estimate the top 5% of the task(like if there are 100 tasks i want to display top 20 tasks which consumed maximum hours).

My query is given below

base search|stats sum(hours) by task,domain,group.

Please help me to estimate the top 5% of the task.

Tags (1)
0 Karma

New Member

Hi,

probably this is not a best solution but it works.
just add this to the end of your query:

base search|stats sum(hours) as hours by task,domain,group
| sort - hours 
| streamstats count
| eventstats count as max_count
| eval percent = round(exact(exact(count * 100) / max_count), 0)
| search percent <= 5
| fields - count, max_count, percent

PS as FrankVl said I missed the "sort" command,
so I edited this post to add it.

0 Karma

Ultra Champion

You probably would want to do a sort on the sum(hours) first.

0 Karma

New Member

Hi FrankVl,

yea, I missed it.
So the query should be:
base search|stats sum(hours) as hours by task,domain,group
| sort - hours
| streamstats count
| eventstats count as max_count
| eval percent = round(exact(exact(count * 100) / max_count), 0)
| search percent <= 5
| fields - count, max_count, percent

0 Karma