Lag Window Functions gave me split rows

user05012 · February 2021

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?

user05012 · February 2021

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 · February 2021

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

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
Jae has a great youtube video about window functions here: https://www.youtube.com/watch?v=eifSYZIcPzg&t=361s

Hope this helps

jaeW_at_Onyx · February 2021

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

user05012 · March 2021

Thanks @jaeW_at_Onyx & @MarkSnodgrass , adding an additional column works. Hopefully DOMO can fix this problem.

Answers