Splunk Search

## Can you help me with the following conditional streamstats issue?

Explorer

Hi splunkers,

Suppose I have the following table:

Date ItemsPurchased UnitPrice
1/1/1111 20 0.5
2/1/1111 10 1
3/1/1111 -7 0
4/1/1111 8 0.2

Which is basically a representation of my stock, where the -7 means that 7 items have been sold.

So now I want to calculate the Median Unit Price, which I do by using the following query:

``````| streamstats sum(ItemsPurchased) as GTotal |streamstats sum(eval(ItemsPurchased*UnitPrice)) as UTotal  |eval MedianUnitPrice= UTotal / GTotal  |table date ItemsPurchased UnitPrice GTotal  UTotal MedianUnitPrice
``````

This works fine, calculating the MedianUnitPrice as required, HOWEVER, it also tries to calculate it for my Sale (-7), which skews the results thereon..

Date ItemsPurchased UnitPrice MedianUnitPrice
1/1/1111 20 0.5 0.5
2/1/1111 10 1 0.6667
3/1/1111 -7 0 (-0.475)
4/1/1111 8 0.2 (wrong result since it's adding -0.475 to the calculation)

What I'd like to do is to keep calculating the MedianUnitPrice EXCEPT when ItemsPurchased is a negative value.

Thanks!

Tags (2)
1 Solution
SplunkTrust

Try adding this to your search `sum(eval(if(ItemsPurchased<0,0,ItemsPurchased)*UnitPrice)) as UTotal`

You might need to do it for GTotal as well if you are only considering purchases and not the stock

Happy Splunking!
SplunkTrust

Try adding this to your search `sum(eval(if(ItemsPurchased<0,0,ItemsPurchased)*UnitPrice)) as UTotal`

You might need to do it for GTotal as well if you are only considering purchases and not the stock

Happy Splunking!
Explorer

Hi Renjith,
I managed to solve this one using the filldown function. Thank you very much anyways.

Explorer

Hi Renjith!
I'm getting somewhere, but not there yet.
I managed to zero the UnitPrice as per picture below, but what I need to do now is to copy the last valid UnitPrice (0.00000576) across to where UnitPrice is 0.
So whenever BuyT and SellT = 0, copy the last valid UnitPrice.

Does that make sense?
Thanks!

alt text

Explorer

Hi Renjith, I had a similar query to the one you suggested, but thanks anyways.
what's actually happening here is that the MedianUnitPrice is calculating fine, until I hit the sale transaction (ItemsPurchased is a negative value).
What I'd like it to do is for the MedianUnitPrice to be the same as the one above if ItemsPurchased<0
On the example below where MedianUnitPrice=0, it should actually be 0.05959825, but I don't know how to prevent it from being recalculated. Any ideas?

SplunkTrust

From your search, MedianUnitPrice is calculated from UTotal / GTotal . So we need to fix UTotal as mentioned above. If you do not want the condition applied for UTotal , then make it as a separate variable and calculate median from that. I am trying with the below run anywhere example and you could see that if the value of unit is <0, it just takes the last value. Let me know what difference you need.

``````|makeresults count=5|eval unit=1|accum unit|eval unit=if(unit%2==0,unit*-1,unit) |streamstats sum(eval(if(unit<0,0,unit)*1)) as sum
``````
Happy Splunking!
Explorer

Hi Renjith, please see comment below with picture.

Explorer

Hi Renjith, please see picture below.

Get Updates on the Splunk Community!

#### Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

#### NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

#### Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...