summing 1st and 2nd row, and then summing 1st, 2nd, and 3rd, and then summing 1st, 2nd, 3rd, 4th
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!
Best Answer

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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers

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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 
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 dropdowns. (Sorry I'm new to Domo and trying to figure some of the more advanced functionalities out)
1 
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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
 10.5K All Categories
 8 Connect
 918 Connectors
 250 Workbench
 470 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 477 Datasets
 194 Visualize
 253 Beast Mode
 2.1K Charting
 11 Variables
 17 Automate
 354 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 247 Distribute
 63 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 174 Product Ideas
 1.2K Ideas Exchange
 12 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive