Splunk Search

How to calculate moving average based on 2 fields?

ADRIANODL
Explorer

Hi Splunkers,
Suppose I have 2 values in my seach:

Date, # of items purchased, UnitPrice
Day1, 4, 0.12
Day2, 10, 0.10
Day3, 25, 0.19
Day4, 100, 0.15

I want to calculate the average item price throughout time. I hope this question makes sense.

Cheers,

Tags (1)
0 Karma

himpor
Engager

base query
| eval purchase_price = (unit_price * no_of_items)
| stats avg(purchase_price)

0 Karma

himpor
Engager

[your search ] | eval purchaseprice = (item_no * unit_price) | eval total_items = sum(item_no) | eval totalpurchaseval = sum(purchaseprice) | eval averageitemprice= totalpurchaseval/total_items

0 Karma

493669
Super Champion

If you want avg for item price then try below-

|eventstats avg(UnitPrice) as avg|eval avg=round(avg,2)
0 Karma

ADRIANODL
Explorer

Sorry but this doesn't solve my problem since the average I want to calculate is derived from # of items purchased AND UnitPrice.

0 Karma

493669
Super Champion

@ADRIANODL, So what is the expected output?
like for # of items purchased=4 and UnitPrice=0.12 then how avg is required to be calculated?

0 Karma

ADRIANODL
Explorer

Hi mate,
If for example, I buy 4 apples for 2 dollars each on day one, and 6 apples for 3 dollars each on day two, the average cost of my apples is calculated somehow like that:

Total average is ((4 x$2) + (6 x $3))/(4+6) = 2.6 dollars average per apple.

Now imagine I have X number of days, with different amounts of apples, and different value amounts).

I'm having a hard time translating this equation into splunk commands.

0 Karma

493669
Super Champion

try this run anywhere search-

| makeresults |eval  apple=4,price=2|append[| makeresults |eval  apple=6,price=3]|eval product=apple*price|eventstats sum(apple) as sumApple, sum(product) as sumproduct|eval avg=sumproduct/sumApple|fields apple price avg
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...