Splunk Search

Sum if formula for daily, hourly, and monthly rates

jackreeves
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

0 Karma
1 Solution

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

woodcock
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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

niketn
Legend

@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
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jackreeves
Explorer

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

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...