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
@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)
| addcoltotals rate
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
What's the expected output based on these example values?
@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)
| addcoltotals rate
Thanks that has worked perfectly! Knew I was over-complicating things!
@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. 🙂