Aggregation on Lag/Lead functions

javiermolina

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

GrantSmith

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.

javiermolina

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

javiermolina

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

GrantSmith

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

jaeW_at_Onyx

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

javiermolina

Thank you for the help. I will try that way