Lag Window Functions gave me split rows

I am trying to use window functions for lag/lead. However no matter how I try, the table still show splits.

lag(sum(case when `control or target` = 'target' then `vol_usd` else 0 end)) over (partition by year(`trade_date`)*100 + month(`trade_date`))

Is there anything wrong here?

Answers

  • Sorry I used order by instead of partition. But in either case, both wont sum up but gave me 31 rows of 202101.

    lag(sum(case when `control or target` = 'target' then `vol_usd` else 0 end)) over (order by year(`trade_date`)*100 + month(`trade_date`))

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @jaeW_at_Onyx and @GrantSmith are the kings of window functions, so I am sure they can help you out. I would point you to these two resources:

    1. You can perform window functions in Magic ETL and they run very fast. Here is the KB article: https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window
    2. Jae has a great youtube video about window functions here: https://www.youtube.com/watch?v=eifSYZIcPzg&t=361s


    Hope this helps

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    lag(sum(case when `control or target` = 'target' then `vol_usd` else 0 end)) over (partition by year(`trade_date`)*100 + month(`trade_date`))
    

    I think Domo broke / changed functionality.

    You can no longer use an expression in the partition or order by clause -- year(`trade_date`)*100 + month(`trade_date`)
    

    add this to your dataset as a hardcoded column

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks @jaeW_at_Onyx & @MarkSnodgrass , adding an additional column works. Hopefully DOMO can fix this problem.