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