How can I group last results into OTHERS?

[anoopk1981]

Hi,

I have a search that gives me results as below

"Country" "Sales" "Total Sales" "Percentage"

 A                  300         1855           16.5
 B                  250         1855           13.2
 C                  200         1855           10.9
 D          190         1855           10.2
 E                  180         1855            9.7
 F                  160         1855            8.6
 G          155         1855            8.3
 H          145         1855            7.8
 I                   140    1855            7.5
 J                   135    1855            7.2 

Now I want to show only the first 5 rows and group the remaining rows into Others..something as below

"Country" "Sales" "Total Sales" "Percentage"

 A                  300         1855           16.5
 B                  250         1855           13.2
 C                  200         1855           10.9
 D          190         1855           10.2
 E                  180         1855            9.7

OTHERS 735 1855 39.6

Thank You

[Vijeta]

Try this

index="marketing1" sourcetype="csv" | eventstats sum("Declared Value _USD") as total_sales |stats sum("Declared Value _USD") as sales max(total_sales) as total_sales by "Destination Country Correct Translation"|eval Percentage=sales/total_sales*100 |sort 0 - sales| rename "Destination Country Correct Translation" as Country

| sort 0 - Percentage
|streamstats count as c
| eval Country=if(c>5,"Other",Country)
| stats sum(sales) as Sales sum(Percentage) as Percentage max("total_sales") as "Total Sales" by Country

[anoopk1981]

Yes. That worked. Than You

[Vijeta]

Please accept the answer if that worked for you. 🙂

[dstile]

Put an eval like this before the transform command that gives you the Sales by Country:

eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")

for example:

index="myindex"
| eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")
| stats count as Sales by Country
| eventstats sum(Sales) as TotalSales
| eval Percentage = Sales / TotalSales

[anoopk1981]

This is my main search

index = "myindex" | eventstats sum("Declared Value _USD") as total_sales |stats sum("Declared Value _USD") as sales max(total_sales) as total_sales by "Destination Country Correct Translation"|eval Percentage=sales/total_sales*100 |sort 0 - sales

So where do you suggest to put your statements?

[dstile]

index = "myindex"
| eval "Destination Country Correct Translation" = case("Destination Country Correct Translation"=="A","A","Destination Country Correct Translation"=="B","B","Destination Country Correct Translation"=="C","C","Destination Country Correct Translation"=="D","D","Destination Country Correct Translation"=="E","E",true(),"Others")
| stats sum("Declared Value _USD") as sales by "Destination Country Correct Translation"
| eventstats sum(sales) as total_sales
| eval Percentage=sales/total_sales*100
| sort 0 - sales

[anoopk1981]

I tried but got this result

Destination Country Correct Translation sales Percentage total_sales
Others 1855 100 1855

[dstile]

I think you got bit by an issue Splunk has with using fields that include spaces in evals. A rename of the Destination Country Correct Translation to a field name with no spaces would have sorted it.

index = "myindex"
| rename "Destination Country Correct Translation" as Country
| eval Country = case(Country=="A","A",Country=="B","B",Country=="C","C",Country=="D","D",Country=="E","E",true(),"Others")
| stats sum("Declared Value _USD") as sales by Country
| eventstats sum(sales) as total_sales
| eval Percentage=sales/total_sales*100
| sort 0 - sales

[mstjohn_splunk]

hi @anoopk1981

Did the answer below solve your problem? If so, please resolve this post by approving it!
If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

[Vijeta]

You can use at the end of your query- |top limit=10 Percentage useother=1

[anoopk1981]

top won't work. top gives me the values by the most number of occurrences . This is entirely different .

[Vijeta]

Try this

your search
| sort 0 - Percentage
|streamstats count as c
| eval Country=if(c>5,"Other",Country)
| stats sum(Sales) as Sales sum(Percentage) as Percentage sum("Total Sales") as "Total Sales" by Country

[anoopk1981]

It gave me only the Other row..not the first five rows + Other .

[Vijeta]

Please check the value of c generated using streamstats. The value should be 1,2,3,4,5,6,....
Also please make sure the fieldname from your query is same as the fieldname used for Country . It will be helpful if you can paste your code here.

[Vijeta]

You need to add this command after your query and before streamstats

|rename “Destination Country Correct Translation" as Country

[anoopk1981]

This is my current query..

index="marketing1" sourcetype="csv" | eventstats sum("Declared Value _USD") as total_sales |stats sum("Declared Value _USD") as sales max(total_sales) as total_sales by "Destination Country Correct Translation"|eval Percentage=sales/total_sales*100 |sort 0 - sales

[DalJeanis]

Here's one way ...

your search
| sort 0 - Percentage
| streamstats count as recno
| appendpipe 
    [ | where recno > 5 
      | stats sum(Sales) as Sales sum(Percentage) as Percentage by "Total Sales" 
      | eval Country = "OTHER" 
      | eval recno=5.5 
     ]
| where recno <6
| fields - recno

[anoopk1981]

Thank You.

[anoopk1981]

I tried it but in the result it shows only the first 5 rows..

"Country" "Sales" "Total Sales" "Percentage"

A 300 1855 16.5
B 250 1855 13.2
C 200 1855 10.9
D 190 1855 10.2
E 180 1855 9.7