Can you help me with the following conditional streamstats issue?

[ADRIANODL]

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!

[renjith_nair · Accepted solution]

@ADRIANODL,

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

[ADRIANODL]

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

[ADRIANODL]

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

[ADRIANODL]

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?

[renjith_nair]

@ADRIANODL,

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

[ADRIANODL]

Hi Renjith, please see comment below with picture.

[ADRIANODL]

Hi Renjith, please see picture below.