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
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.3K Connect
 1.1K Connectors
 273 Workbench
 1 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 525 Datasets
 2.1K Magic ETL
 2.9K Visualize
 2.2K Charting
 434 Beast Mode
 22 Variables
 510 Automate
 114 Apps
 388 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 66 Manage
 66 Governance & Security
 1 Product Release Questions
 Community Forums
 40 Getting Started
 26 Community Member Introductions
 67 Community Announcements
 4.8K Archive