How to find the average of top (max) values of a field sorted by other fields?

[andrewhlui]

I have the following table of data generated by a search:

category a category b  count 
         A          E      1
         A          F     10
         B          E      2
         B          E     12
         B          F      3
         B          F      5
         A          E     10
         A          E      2
         A          E     15

I want to find the average of the top 3 values (or any other arbitrary number of values) for each grouping.

So for (A, E) the values would be:

1, 2, 10, 15 -> top 3 values are 2,10,15 -> average of 9

The resulting table should look something like this:

         E      F
  A      9     10
  B      7     4

How should I do this?

[somesoni2]

GIve this a try. You would need to update the | dedup N... to configure how many top values you want to keep.

your current search
| sort 0 category_a category_b -count
| dedup 3 category_a category_b
|chart avg(count) by category_a category_b

[cmerriman]

I would start with a streamstats.

...base search|sort 0 - count category_a category_b|streamstats count as top by category_a category_b|where top<4|chart avg(count) by category_a category_b

http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Streamstats
with streamstats and sort, you should be able to filter the top three of each category set and average them with chart.