How to round Totals for Statistics under Format Summary?

[LearningGuy]

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

[LearningGuy]

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

[yuanliu]

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

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]

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

[yuanliu]

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]

@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

[bowesmana]

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]

@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)

[bowesmana · Accepted solution]

@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]

@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)

[bowesmana]

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

[bowesmana]

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

| addcoltotals

[LearningGuy]

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

[PickleRick]

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.