Aggregation on Lag/Lead functions

I used the Lag function to operate a subtraction between data point and previous data point. However now I need now the average for that new column (which I am not able to do in Beast Mode)

Any suggestions


Comments

  • GrantSmith
    GrantSmith Indiana 🥷

    This isn't possible as you can't aggregate a window function within a beast mode. What you'd need to do is perform your lag in an ETL and then calculate the average in your card. You may lose some filtering abilities as it's going to be pre-aggregated.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Thanks for the info Grant

    Are you aware of any other type of statistical functions as Standard deviation within (not overall) or ppk / cpk parameters? I am actually doing this in order to manually calculate it

  • And do you have any tutorial on how to do it with ETL

  • GrantSmith
    GrantSmith Indiana 🥷

    You can utilize a Rank & Window tile (see https://domohelp.domo.com/hc/en-us/articles/360044876094-Magic-ETL-Tiles-Aggregate#3.) within Magic ETL to select the LAG value of your column.

    Domo does offer a Data Science suite of tiles which includes the outlier detection tile to calculate standard deviation but this is a premium feature. https://domohelp.domo.com/hc/en-us/articles/360045259294-Magic-ETL-Tiles-Data-Science#h_11ce7566-b3c0-486c-950e-63743079b9df

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    sorry for showing up late to the party. the problem isn't that you can't aggregate a window function as @GrantSmith suggests.

    I believe the issue is that you're trying to perform math on an aggregation and an unaggregated value (column11) in your example.


    you SHOULD be able to write

    lag(sum(col_11), 1) over( order by date desc ) - sum(col_11) 
    
    

    but you CANNOT write

    lag(sum(col_11), 1) over( order by date desc ) - col_11 
    

    because col_11 in example 2 only exists pre-aggregation. and your lag() function applies after aggregation , sum(col_11)


    the ABS has nothing to do with the error. this should work fine.

    abs(
    lag(sum(col_11), 1) over( order by date desc ) - sum(col_11) 
    )
    
    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"
  • Thank you for the help. I will try that way