Splunk Search

How to round Totals for Statistics under Format Summary?

LearningGuy
Builder


Hello
When I turned on Total for Statistics under Format > Summary, the output shows long digit after decimal point: Total: 1129.3600000000001
How do I round this number to 1129 or 1130?   Thank you

 

| makeresults format=csv data="Student, Score
a,153.8
b,154.8
c,131.7
d,115.4
e,103.2
f,95.4
g,95.4
h,93.2
i,93.2
j,93.26"
| table Student, Score

 

CloudGuy_0-1701974670546.png

 

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@LearningGuy the eval statement is applied to all events, so based on using the eval if(isnull()... test, that would not do anything if the Student column is non-null. In your case, if you only want to floor() the Score for the total column you would need to do

| eval Score=if(Student="Total", floor(Score), Score)

so you ONLY round the Score if it's the Total row

View solution in original post

LearningGuy
Builder

addcoltotals will show up at the end of the row, so if i have multipages, it will now show on the first page
Why  Splunk get 1129.3600000000001 from?
The correct total should be 1129.36

Thanks

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Only addtotals is appended to the end of rows.  Have you tried addcoltotals as @bowesmana recommended?  If you did, you would have gotten this:

addcoltotals.png

Now, floating-point arithmetics is always tricky with digital computers.  But artifacts usually shouldn't show in such small jobs.  Then, again, Splunk is known to be quirky in many computations. (addcoltotals does not have this problem.)

You wanted the total (1129.36) to be rounded up to 1130.  This can be done after addcoltotals.  For example,

| makeresults format=csv data="Student, Score
a,153.8
b,154.8
c,131.7
d,115.4
e,103.2
f,95.4
g,95.4
h,93.2
i,93.2
j,93.26"
| table Student, Score
| addcoltotals
| eval Score = if(isnull(Student), floor(Score) + 1, Score)

 

LearningGuy
Builder

Hello @yuanliu 

addcoltotals will show up at the end of the row, so if I have multipages, it will now show on the first page

In the real data, I  have more than 10, so addcoltotals will not show up on the front page

Why  did Splunk get 1129.3600000000001, not 1129.36?

Thanks

0 Karma

yuanliu
SplunkTrust
SplunkTrust
Why  did Splunk get 1129.3600000000001, not 1129.36?

As I already said, floating point arithmetics is messy on digital computers. But Splunk is also known to be quirkier. You can report this as a bug.

In the real data, I  have more than 10, so addcoltotals will not show up on the front page

Given that Summary has that bug, you can decide whether to use addcoltotals as a workaround, or live with 1129.3600000000001. SPL doesn't control pagenation. Also, I don't believe Summary offers rounding.

LearningGuy
Builder

@yuanliu 
Using addcoltotals
1) How do I round the score 1129.36 to 1129?
2) How do I remove No 11, but keep No 1-10?
See below.  Thank you so much for your help

CloudGuy_1-1702436814365.png

 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Rounding down is done using floor, as you have tried, but you are adding +1 so it will be 1130

You have row numbers turned on in the format menu, so you cannot influence row numbers to remove the 11, but you can make your own row numbers by adding this

``` Round Score DOWN ```
| eval Score = if(isnull(Student), floor(Score), Score)

``` Now do row processing ```
| streamstats count as Row
| eval Row=if(isnull(Student), null(), Row)
| table Row Student Score

 

LearningGuy
Builder

@bowesmana 

Does the "Score" variable on "eval" pipe always indicate a total Score if applied after "addcoltotals"?
Thank you so much.

| addcoltotals
| eval Score = if(isnull(Student), floor(Score), Score)

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@LearningGuy the eval statement is applied to all events, so based on using the eval if(isnull()... test, that would not do anything if the Student column is non-null. In your case, if you only want to floor() the Score for the total column you would need to do

| eval Score=if(Student="Total", floor(Score), Score)

so you ONLY round the Score if it's the Total row

LearningGuy
Builder

@bowesmana @yuanliu 

Thank you for your help.

I understand now if I want to use Student="Total", I just use the following

| addcoltotals labelfield=Student label=Total
| eval Score = if(Student="Total", floor(Score) + 1, Score)



if I leave the label blank, I can use

| eval Score = if(isnull(Student), floor(Score) + 1, Score)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

You are still rounding UP with your floor(Score)+1 - is that what you intended?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

I don't know if it's possible, but you can add your own totals by appending

| addcoltotals

LearningGuy
Builder

Hello @bowesmana 
addcoltotals will show up at the end of the row, so if i have multipages, it will now show on the first page

Why  Splunk get 1129.3600000000001 from?
The correct total should be 1129.36

Thanks

0 Karma

PickleRick
SplunkTrust
SplunkTrust

The total in your case is calculated by the visualization itself (which means your browser). Apparently the JS which calculates the totals treats all your row values as floating point numbers. And floating number calculations do tend to lead to ugly rounding errors.

You could file a bug on that because it's indeed ugly.

Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...