topic Re: Tiered pricing data calculation in Getting Data In

Tiered pricing data calculation

phoenixdigital — Thu, 23 May 2013 01:32:14 GMT

Hi All,

I have a CSV file with the following information

date,retailer,lower,upper,price_rate
20120621,retailer1,0,10,10
20120621,retailer1,10,20,8
20120621,retailer1,20,100,5
20120621,retailer2,0,10,10
20120621,retailer2,10,20,8
20120621,retailer2,20,100,5

Now there are Splunk events which contains hourly usage

_time, retailer, usage

So I want to get a sum of all the usage over one month with something like

sourcetype="myData" | stats sum(usage) by retailer

But I then want to calculate a total cost.

So for example the if the total usage over the month was 50.

The first 10 would be priced at $10 per unit
The second 10 units would be priced at $8 per unit
The remaining 30 units would be priced at $5 per unit

How can I do this in a Splunk search?

Harder still the original CSV was actually intended to be a time based lookup so it would only use the associated date section based on the _time from the events.

I am thinking maybe I should restructure this CSV and join it 3 times in the props.conf somehow???

Any assistance would be greatly appreciated.

Re: Tiered pricing data calculation

chris — Thu, 23 May 2013 22:27:51 GMT

im skipping the time based part for now. If you do the lookup after your stats command:

sourcetype="myData" | stats sum(usage) as sum by retailer   | lookup retail retailer

the lookup will add the tiered pricing information as multivalued fields.

You can append this eval to get your result ( I don't think, that Splunk has any looping operators that can be aplied to an event/the mv fields of an event):

|eval price=case (
sum>mvindex(lower,0) AND sum<=tonumber(mvindex(upper,0)),tonumber(mvindex(price_rate,0))*sum,

sum>=tonumber(mvindex(lower,1)) AND sum<=tonumber(mvindex(upper,1)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+(sum-tonumber(mvindex(upper,0)))*tonumber(mvindex(price_rate,1)),


sum>=tonumber(mvindex(lower,2)) AND sum<=tonumber(mvindex(upper,2)),tonumber(mvindex(price_rate,0))*tonumber(mvindex(upper,0))+tonumber(mvindex(price_rate,1))*(tonumber(mvindex(upper,1))-tonumber(mvindex(upper,0)))+(sum-tonumber(mvindex(upper,1)))*tonumber(mvindex(price_rate,2))
)

This obviously only works if you you have only 3 ranges and gets more complex with every additional range you add.

Re: Tiered pricing data calculation

phoenixdigital — Fri, 24 May 2013 00:44:37 GMT

Nice that works perfectly. A good example of how multikv works too.

My only issue now is the using this as a temporal style lookup.

Turns out it is possible by adding a max_matches=3 to the transforms.conf . Again however as you mentioned if some retailers have less/more tiers than others it breaks the whole system.

Re: Tiered pricing data calculation

phoenixdigital — Mon, 28 Sep 2020 13:58:17 GMT

I'm thinking the best solution is to just change the CSV format to.

date,retailer,upper_tier1,price_tier1,,upper_tier2,price_tier2,upper_tier3,price_tier3

Then use a similar method as you suggested. That will allow me to use temporal lookups without worrying about changes in the number of tiers. If they add additional tiers I will just have to change my searches.

Thanks again for the assist.