Archive
Highlighted

Calculating total in row

New Member

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

Tags (3)
0 Karma
Highlighted

Re: Calculating total in row

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Calculating total in row

New Member

Thank you! I will give this a try

0 Karma
Highlighted

Re: Calculating total in row

SplunkTrust
SplunkTrust

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)
0 Karma
Highlighted

Re: Calculating total in row

New Member

Thank you! I will try this

0 Karma
Highlighted

Re: Calculating total in row

Esteemed Legend

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!!!!

0 Karma
Highlighted

Re: Calculating total in row

Esteemed Legend

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
0 Karma
Highlighted

Re: Calculating total in row

New Member

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

0 Karma
Highlighted

Re: Calculating total in row

Esteemed Legend

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.

0 Karma
Highlighted

Re: Calculating total in row

Esteemed Legend

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

0 Karma