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!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...