topic Re: Calculate w different prices based on cliplevels in Splunk Search

Calculate w different prices based on cliplevels

JYTTEJ — Mon, 28 Sep 2020 14:09:35 GMT

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?

Re: Calculate w different prices based on cliplevels

aholzer — Mon, 24 Jun 2013 17:09:07 GMT

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.

Re: Calculate w different prices based on cliplevels

lguinn2 — Mon, 24 Jun 2013 20:40:03 GMT

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)

Re: Calculate w different prices based on cliplevels

JYTTEJ — Fri, 12 Jul 2013 11:07:03 GMT

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?

Re: Calculate w different prices based on cliplevels

lguinn2 — Mon, 15 Jul 2013 16:01:27 GMT

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