My data in Splunk contains information about sales from different store branches. More specifically, I have the data in the format - Date, Branch_ID, Sales (Number), that describes for each branch, its sales stats overview for each day (one number).
I would like to run a search that will calculate for each branch, the average of the top 5 best sales day, across the last month. More specifically, I would like to be able to reach results in the format Branch_ID Avg(Max(5)).
For example, if branch_id #1 has the values 0,100,50,100,25,200,75,0,150,25, I would like the query to output #1 (200+150+100+100+75)/5=125.
I tried the stats max, but it only takes the first max value. I tried to sort with limit count, but I couldn't apply the count for each branch.
Any ideas would be appreciated.
Please post a few sample events. Also, describe how the sales values are extracted as fields (if they are);
- a single event per branch with separate fields, or
- a single event per branch with a multivalued field, or
- separate events with a single sales field in each.
Assuming the last scenario, where each sale would be in a separate event;
... | sort - sales_field | dedup 5 branch_id | stats avg(sales_field) as "Avg Top5 Sales" by branch_id
This seems somewhat inefficient, but right now I can't think of anything better.