topic Re: How to round Totals for Statistics under Format Summary? in Splunk Search

How to round Totals for Statistics under Format Summary?

LearningGuy — Thu, 07 Dec 2023 18:48:53 GMT

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

Re: How to round Totals for Statistics under Format Summary?

bowesmana — Fri, 08 Dec 2023 00:11:26 GMT

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

| addcoltotals

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Fri, 08 Dec 2023 01:53:41 GMT

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

Re: How to round Totals for Statistics under Format Summary?

yuanliu — Fri, 08 Dec 2023 09:28:04 GMT

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)

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Fri, 08 Dec 2023 13:55:57 GMT

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

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Fri, 08 Dec 2023 13:58:08 GMT

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

Re: How to round Totals for Statistics under Format Summary?

yuanliu — Fri, 08 Dec 2023 20:01:14 GMT

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.

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Wed, 13 Dec 2023 03:07:42 GMT

@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

Re: How to round Totals for Statistics under Format Summary?

bowesmana — Wed, 13 Dec 2023 21:34:38 GMT

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

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Fri, 15 Dec 2023 14:36:07 GMT

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

Re: How to round Totals for Statistics under Format Summary?

PickleRick — Fri, 15 Dec 2023 15:00:02 GMT

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.

Re: How to round Totals for Statistics under Format Summary?

bowesmana — Mon, 18 Dec 2023 00:47:04 GMT

@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

Re: How to round Totals for Statistics under Format Summary?

LearningGuy — Mon, 18 Dec 2023 16:52:03 GMT

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

Re: How to round Totals for Statistics under Format Summary?

bowesmana — Mon, 18 Dec 2023 21:27:32 GMT

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