Skip to content

    • Magic ETL

    Magic ETL

    Aggregation on Lag/Lead functions

    Member
    in Magic ETL

    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

    Screenshot 2022-08-22 152812.jpg Screenshot 2022-08-22 152842.jpg


    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Comments

    • Coach

      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!**
    • Member

      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

    • Member

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

    • Coach

      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!**
    • Coach

      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

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

      but you CANNOT write

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

      1. abs(
      2. lag(sum(col_11), 1) over( order by date desc ) - sum(col_11) 
      3. )
      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"
    • Member

      Thank you for the help. I will try that way

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories