Calculating total in row

[khanyag1]

Hi,

I need help adding b+ c together to get a total, I will then calculate a percentage using a/combined b+c. Is this possible?

| stats dc(Users) as UsersCount by Label app
| stats sum(UsersCount) by Label

Label sum(UsersCount)
1 a 14
2 b 2
3 c 19

[woodcock]

It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! You need to preserve the actual values all the way through to the very end like this:

... | stats values(Users) AS Users BY Label 
| eval foo="bar" 
| xyseries foo Label Users 
| eval AplusB = mvappend(A, B)
| foreach * [ eval "<<FIELD>>"=mvjoin('<<FIELD>>', ":::") ]
| untable foo Label Users
| fields - foo 
| eval AplusB = if(Label = "AplusB", Users, null()) 
| eventstats first(AplusB) AS AplusB
| foreach Users AplusB [ eval "<<FIELD>>" = mvcount(split('<<FIELD>>', ":::")) ]

Here is a run-anywhere example:

index=_* 
| rename component AS Users, log_level AS Label 
| replace "INFO" WITH "A", "ERROR" WITH "B"
| stats values(Users) AS Users BY Label 
| eval foo="bar" 
| xyseries foo Label Users 
| eval AplusB = mvappend(A, B)
| foreach * [ eval "<<FIELD>>"=mvjoin('<<FIELD>>', ":::") ]
| untable foo Label Users
| fields - foo 
| eval AplusB = if(Label = "AplusB", Users, null()) 
| eventstats first(AplusB) AS AplusB
| foreach Users AplusB [ eval "<<FIELD>>" = mvcount(split('<<FIELD>>', ":::")) ]

[woodcock]

NO, NO, NO!!! It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!!

[woodcock]

What you are doing is NOT MATHEMATICALLY CORRECT!!!; you cannot sum distinct counts; think about it...
So do this:

| stats dc(Users) AS UsersCount BY Label
| eval foo="bar"
| xyseries foo Label UsersCount
| eval AplusB = A + B
| untable foo Label UsersCount
| fields - foo
| eval AplusB = if(Label = "AplusB", UsersCount, null())
| eventstats first(AplusB) AS AplusB

[khanyag1]

If my Label displays three labels, how do I extract the labels so that I can add them? Thanks for your response!

[woodcock]

Keep in mind that this answer was for EDUCATIONAL PURPOSES ONLY. It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!! I am working on a solution that will work. Stand by.

[woodcock]

See my other answer. It does it the right way keeping the values, not the counts.

[somesoni2]

Try this (assuming field Label has values "a" "b" "c". If there are different values, update the column name in the query)

..your base earch
| chart dc(Users) as UsersCount by app Label | fields - app
| stats sum(*) as *
| eval total=b+c
| eval percentage=round*a*100/total,2)

[khanyag1]

Thank you! I will try this

[woodcock]

NO, NO, NO!!! It is NOT MATHEMATICALLY VALID TO SUM distinct counts!!! DO NOT DEPLOY THIS. Although this answer does what you asked, you should not be asking this. It is as wrong as can possibly be. This is bad math, and will lead to WRONG DECISIONS!!!!

[adonio]

little clumsy solution, but maybe itll work for you
try it anywhere

| makeresults count=1
| eval data="1 a 14;;;2 b 2;;;3 c 19"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<label>[^\s]+)\s(?<app>[^\s]+)\s(?<tot_users>[^\s]+)"
| table label app tot_users
| rename COMMENT as "above generates fake data, below is solution"
| chart max(tot_users) as total over label by app
| stats values(*) as *
| mvexpand label
| eval b_plus_c = b + c
| eval percent = round(a / b_plus_c * 100, 2)

hope it helps

[khanyag1]

Thank you! I will give this a try