summing 1st and 2nd row, and then summing 1st, 2nd, and 3rd, and then summing 1st, 2nd, 3rd, 4th

user03855

Using beastmode (or another method) is there a way to sum (in a new column appearing directly to the right of the values) the 1st and 2nd row, and then sum 1st, 2nd, and 3rd rows (appearing directly beneath previously sum of 1st and 2nd row), and then summing 1st, 2nd, 3rd, 4th and so on an so forth until a few hundred or so...like is there a way to drag a formula down that works or write a function using a case statement that add functions together from top to bottom. Thanks, looking forward to hearing back!

AS

You might have to tinker with that.  It's not a fully supported function at this time.

Magic ETL does not have a SQL edit formula transform, but that's long been asked for.  You'll have to use the Rank & Window transform, which is excellent and closely follows the function outlined above.

 

1) Sum your amount with unbounded preceding rows to 0 following rows (sum everything before but none after), click apply.

2) Order the function by whatever column is your sort, either 3) ascending or descending

4) Optionally add a partition if you want there to be multiple parallel sums by different dimensional breakdowns like product line or customer group or country.

 

AS

You'd use a window function for that either in beast mode or in dataflow.

We have one that looks like this in a dataflow:

sum(usd_amount) OVER (partition by customer_group rows unbounded preceding)

Which is to say, for every row, broken out by customer group, sum up the amount of all preceding rows in the data.  So on the first row for a particular customer group, the new amount is just the amount of the first row, but the second row is the sum of both the first and the second.  That "window" starts over for each new customer group.  You don't need partitioning, though.

Beast mode handles these functions differently.  In beast mode you might have to do something like:

SUM(SUM(amount) OVER (ROWS UNBOUNDED PRECEDING))

 

Oh yeah, and you might have to throw in an ORDER BY in there to make sure your rows are in order.

SUM(SUM(amount) OVER (ORDER BY row_id ROWS UNBOUNDED PRECEDING))

 

Let me know if that works.

user03855

I am trying to figure out the function in Beastmode, it keeps coming back as a syntax error.

Also, when I am trying to use the formula in ETL, there is not a place to put a full formula in the window function, it is broken up into different drop-downs. (Sorry I'm new to Domo and trying to figure some of the more advanced functionalities out)

AS

You might have to tinker with that.  It's not a fully supported function at this time.

Magic ETL does not have a SQL edit formula transform, but that's long been asked for.  You'll have to use the Rank & Window transform, which is excellent and closely follows the function outlined above.

 

1) Sum your amount with unbounded preceding rows to 0 following rows (sum everything before but none after), click apply.

2) Order the function by whatever column is your sort, either 3) ascending or descending

4) Optionally add a partition if you want there to be multiple parallel sums by different dimensional breakdowns like product line or customer group or country.