How do I calculate the average of the past 3 sales of an item?

meekey

I have a database of millions of sales over time for a thousands of specific assets. I'd like to have a calculated field that determines the last 3 sales of an item. For some, they may have sold 3 times last month for others 3 times in the past 24 months. Thanks!!!!!

GrantSmith

Hi @meekey

you’ll want to use a lag function to get the last three values. Typically I use this when doing a rolling average like you’re needing. I’ve done a write up on rolling average and lag functions here: https://dojo.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

window functions require your CSM to enable them in your instance to use

meekey

I see so many examples of lag functions that all reference a date, so since I don't care about date necessarily, do I just leave end it with over ()?

(lag((sale_price),1) over( ) +

lag((sale_price),2) over( ) +

lag((sale_price),3) over( ) +

)/3

meekey

...also, I don't see LAG in my list of available functions... Sorry for such basic questions, Grant. Thanks for your help :)

mhouston

@meekey the OVER clause tells you what to partition by - in your case I'd assume you'd want to do it on your asset and then order by date descending so you could find the last 3 sales for each asset.

Something like: lag(sale_price OVER asset ORDER BY sale_date desc)

I think you may have to reach out to your CSM to get the lag function enabled if it doesn't currently work in your instance.