Sum categories from a main search.

[JMPP]

Hi Cummunity team, 

I have a complex query to gather the data below, but a new request came up, it was asked to me to add in the report email subject the product category totals by Category. with the $result.productcat1$ and $result.productcat2$ I could apprach that,
but the way I'm calculating the totals I'm not getting the expected numbers, because I'm appeding the columns from a subquery and transposing the values with  xyseries.

Could you please suggest how can I sum(Sales Total) by productcat1 and productcat2 in a new field but keeping the same output as I have now?, 

e.g.:
something like if ProducCategory="productcat1"; then  productcat1=productcat1+SalesTotal, else productcat2=productcat2+SalesTotal ``` But Print the original output ```
 

Consider productcat1 and productcat2 are fixed values. 

ENV	ProducCategory	ProductName	SalesCondition	SalesTotal	productcat1	productcat2
prod	productcat1	productR	blabla	9	152	160
prod	productcat1	productj	blabla	8
prod	productcat1	productc	blabla	33
prod	productcat2	productx	blabla	77
prod	productcat2	productpp	blabla	89
prod	productcat2	productRr	blabla	11
prod	productcat1	productRs	blabla	6
prod	productcat1	productRd	blabla	43
prod	productcat1	productRq	blabla	55

Thanks in advance.

[JMPP]

Hi @dtburrows3,

Thanks so much it helped me a lot your suggestions, for now I will go with eventstats solutions.

For  foreach command I need to go deep on it since it is more complex.

@PickleRick I will try xyseries, same as I did before to have the expected single values for the productcat# fields. Need to push this report to Production ASAP.

[PickleRick]

You can use eventstats as @dtburrows3 already pointed out, just keep in mind that Splunk will put those sums into every single row in your results. It's not an Excel, you  cannot merge cells here.

[dtburrows3 · Accepted solution]

I think using eventstats can get you the desired output you are looking for if I am interpreting your question correctly.

<base_search>
    | eventstats
        sum(eval(case('ProductCategory'=="productcat1", 'Sales Total'))) as productcat1,
        sum(eval(case('ProductCategory'=="productcat2", 'Sales Total'))) as productcat2
    

Or for a more dynamic approach something like this may work.

<base_search>
    | eventstats
        sum("Sales Total") as overall_sales
            by ProductCategory
    | eval
        overall_sales_json=json_object("fieldname", 'ProductCategory', "value", 'overall_sales')
    | eventstats
        values(overall_sales_json) as overall_sales_json
    | foreach mode=multivalue overall_sales_json
        [
            | eval
                fieldname=spath('<<ITEM>>', "fieldname"),
                field_value=spath('<<ITEM>>', "value"),
                combined_json=if(
                    isnull(combined_json),
                        json_object(fieldname, field_value),
                        json_set(combined_json, fieldname, field_value)
                    )
            ]
    | fromjson combined_json prefix=dynamic_
    | fields - combined_json, overall_sales_json, fieldname, field_value, overall_sales
    ``` Below code is if you only want the new fields on the first row ```
    | streamstats
        count as line_number
    | foreach dynamic_*
        [
            | eval
                <<FIELD>>=if(
                    'line_number'==1,
                        '<<FIELD>>',
                        null()
                    )
            ]
    | fields - line_number
    | rename
        dynamic_* as *