Splunk Search

Calculate w different prices based on cliplevels

JYTTEJ
Communicator

Hi, I need to make a report which need to calculate with two different prices.

Price 3,33 USD for the first 10000 trx within a month

Price 2,22 for 10001 - 20001 trx within same month

Price 1,11 for 20001 + trx within the same month

I have tried following:

|accum Trx as Accum_trx |EVAL Charge=if(Accum_trx <10000,3.33, if(Accum_trx>10001 AND Accum_trx<20001,2.22, 1.11))

If Accum_trx are

3444

7000

9500

10500

the search will calculate 9500*3.33

and 10500 minus 9500 = 1000*2.22

I need to calculate 500 * 3.33 and 500 * 2.22 instead.

How do I accomplish this?

Tags (1)
0 Karma

lguinn2
Legend

Second answer, based on the request to show how the charges were calculated

yoursearchhere
| stats count as totalTransactions by customer
| eval charge = case(totalTransactions < 10001,totalTransactions *3.33,
                     totalTransactions < 20001,33300+(totalTransactions-10000)*2.22,
                     totalTransactions > 20000,55500+(totalTransactions-20000)*1.11)
| eval Transactions = case(totalTransactions < 10001,tostring(totalTransactions),
                     totalTransactions < 20001,"10000\n"+tostring(totalTransactions-10000)),
                     totalTransactions > 20000,"10000\n10000\n"+tostring(totalTransactions-20000))
| eval Rates = case(totalTransactions < 10001,"3.33",
                     totalTransactions < 20001,"3.33\n2.22",
                     totalTransactions > 20000,"3.33\n2.22\n1.11")
| table customer totalTransactions charge Transactions Rates

This seemed simplest, but there are other ways

0 Karma

lguinn2
Legend

Why not:

yoursearchhere
| stats count as numTransactions by customer
| eval charge = case(numTransactions < 10001,numTransactions *3.33,
                     numTransactions < 20001,33300+(numTransactions-10000)*2.22,
                     numTransactions > 20000,55500+(numTransactions-20000)*1.11)
0 Karma

JYTTEJ
Communicator

This works fine! - but I also want to display on the same report how the total has been calculated. I.e. a report showing:

CUSTOMER Trx Trx_Charge Amount
XXX 10000 3.33 33300,00
XXX 10000 2.33 22200,00
XXX 1111 1.11 1234,00

  • Do you have more good ideas?
0 Karma

aholzer
Motivator

Two things I'd suggest:
1) use the case function instead of nested "ifs"
2) use the "delta" command to calculate the difference between the accum_trx of the previous event and your current event

With these two changes you should be able to get what you need.

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...