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
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.
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
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
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) )
Thank you for the help. I will try that way
