Splunk Search

## Sum if formula for daily, hourly, and monthly rates

Explorer

I have some financial data in three separate fields for daily, hourly & monthly rates.
The monthly field always takes precedent but sometimes this field may be blank and have a figure in the daily or hourly column (see below examples). I am struggling to create a sum(eval(if))) formula that will take into account these different permutations. I am able to do this in Excel successful using this formula:

=IF(Monthly<>0,Monthly,IF(Daily<>0,Daily,Hourly)) & this seems to do the trick. Can anyone help replicate this in a sum if formula in SPLUNK please!

Here is my current SPLUNK Search - but this not bringing back correct numbers
mysearch | fields month Company Hourly Daily Monthly "Business Line" | stats sum(eval(if(Daily=0 AND Hourly=0 OR (Daily>0 AND Monthly>0) OR (Hourly>0 AND Monthly>0), Monthly, Null))) as Monthly sum(eval(if(Hourly=0 AND Monthly=0,Daily,Null))) as Daily sum(eval(if(Daily>0 AND Hourly>0 OR (Daily=0 AND Monthly=0), Hourly, Null))) as Hourly

EXAMPLE 1:
Hourly Daily Monthly
ยฃ200 ยฃ1,000 ยฃ30,000

EXAMPLE 2:
Hourly Daily Monthly
ยฃ200 ยฃ0 ยฃ0

EXAMPLE 3:
Hourly Daily Monthly
ยฃ0 ยฃ0 ยฃ25,000

Tags (4)
1 Solution
SplunkTrust

@jackreeves, please try the following eval which give Monthly highest priority, then Daily and least to Hourly.

``````| eval rate=case(Monthly=0 AND Daily=0,Hourly ,Monthly=0 AND Daily!=0,Daily,Monthly!=0,Monthly)
``````

Following is run anywhere search with sample data similar to the question:

``````| makeresults
| eval Monthly=30000,Daily=1000,Hourly =200
| append
[| makeresults
| eval Monthly=0,Daily=1500,Hourly =150]
| append
[| makeresults
| eval Monthly=0,Daily=0,Hourly =220]
| append
[| makeresults
| eval Monthly=25000,Daily=0,Hourly =0]
| fields - _time
| table Monthly Daily Hourly
| eval rate=case(Monthly=0 AND Daily=0,Hourly ,Monthly=0 AND Daily!=0,Daily,Monthly!=0,Monthly)
``````
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Esteemed Legend

Why make it so complicated; just pre-process it like this (which should be more efficient, too):

``````... | eval Hourly=if((Daily>0 OR Monthly>0), 0, Hourly)
| eval Daily=if((Monthly>0), 0, Hourly)
| stats sum(Hourly) AS Hourly sum(Daily) AS Daily sum(Monthly) AS Montly
``````
SplunkTrust

What's the expected output based on these example values?

SplunkTrust

@jackreeves, please try the following eval which give Monthly highest priority, then Daily and least to Hourly.

``````| eval rate=case(Monthly=0 AND Daily=0,Hourly ,Monthly=0 AND Daily!=0,Daily,Monthly!=0,Monthly)
``````

Following is run anywhere search with sample data similar to the question:

``````| makeresults
| eval Monthly=30000,Daily=1000,Hourly =200
| append
[| makeresults
| eval Monthly=0,Daily=1500,Hourly =150]
| append
[| makeresults
| eval Monthly=0,Daily=0,Hourly =220]
| append
[| makeresults
| eval Monthly=25000,Daily=0,Hourly =0]
| fields - _time
| table Monthly Daily Hourly
| eval rate=case(Monthly=0 AND Daily=0,Hourly ,Monthly=0 AND Daily!=0,Daily,Monthly!=0,Monthly)
@jackreeves, I am glad it worked. Just FYI, I had `addcoltotals` for validating that correct rate is being used for Total. I am sure you replaced with the stats command based on rate field. ๐