Sum if formula for daily, hourly, and monthly rates

[jackreeves]

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

[woodcock]

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

[somesoni2]

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

[niketn · Accepted solution]

@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

[jackreeves]

Thanks that has worked perfectly! Knew I was over-complicating things!

[niketn]

@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. 🙂