Archive

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

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

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

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