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

meekey
meekey
in Charting

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

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • meekey
    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
    meekey

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

  • mhouston
    mhouston Contributor

    @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.

Categories