Is it possible to divide all individual values in field1 by the column total of field1?

[demkic]

Hi there,
I am wondering - is it possible to divide values in field1 by the column total of field1 and create a new field2 to display the result of this calculation? For example, I currently have a field called "errors" where I counted the number of errors per error type and I would like to next take those values per error type and divide it by the total number of errors (aka column total). Is this possible?
Thank you!

BASESEARCH
| stats count(eval(success)) as CompletedTransactions by errorType
| eval percent_failure=round(CompletedTransactions*100/TOTAL, 2)
| sort -percent_failure 
| table errorType CompletedTransactions percent_failure total true false percent
| addcoltotals labelfield=errorType label=TOTAL

[vasanthmss · Accepted solution]

try something like this..

Once you are grouping the error type use the event stats to find the column total. so the total will be available for your further calculations.

BASESEARCH
    | stats count(eval(success)) as CompletedTransactions by errorType
    | eventstats sum(CompletedTransactions) as columntotal
    | eval percent_failure=round(CompletedTransactions*100/columntotal , 2)
    | sort -percent_failure 
    | table errorType CompletedTransactions percent_failure total true false percent
    | addcoltotals labelfield=errorType label=TOTAL

Newly added or modified pipes are

| eventstats sum(CompletedTransactions) as columntotal
| eval percent_failure=round(CompletedTransactions*100/columntotal , 2)

Description:

1. Use the eventstats to calcualte the column total and name it as columntotal.
2. use the columntotal field in the percentage calculation.

Hope this will helps you!!!

[demkic]

Actually, I do have one more question regarding this. I would like to actually remove one row in my results but do it in such a way that it does not affect my calculation. Here is an example:

errorType, CompletedTransactions, columntotal, and percent_failure are field1, field2, field3, and field4, respectfully.

field1 field2 field3 field4
approved 120 185 0.65
insufficient funds 50 185 0.27
expired card 10 185 0.05
contact issuer 5 185 0.03

In this case, I would like to removed the entire row where field1=approved but I do not want this to affect the calculations. Reason being is that "approved" is not a failure type and I am only interested in observing the proportion of error types given the total number of transactions processed that result in a failure.

Many thanks!

[demkic]

It worked, thank you for the great feedback!