Running total spilling into a new column
Hi all,
I have a problem where if the 'used' column goes above the 'authorized' column then we need to start adding to the 'used' column below.
For example, when we use over 700m of our authority (total authority is 1.55 billion) then we need to start adding to the 2nd line of 'used'
i.e. if we have used 790 million then 700 would go to the first line and 90 would go to the 2nd.
This would be the output
700 m authorized, 700m used, 0 available
750 m authorized, 90m used, 660m available
I am working in magic etl and am not sure how to set this up. I was thinking a rank and order but am not sure how the logic would work on it.
We have a dataflow that can automatically update the 'used' portion but the authorized portion is coming from a webform that multiple people use.
Does anyone have any insight?
If this helps, feel free to agree, accept or awesome it!
Best Answer

 Hmmm. Maybe something like…
Load and Sort Data: ensure that your data is sorted by the 'Date Authorized' column to process the entries in the correct order.
 Add a Running Total Column: Create a running total for the 'Used' column to keep track of the cumulative usage.
 Calculate the Spillover: For each row, determine if the running total exceeds the 'Authorized' amount, and if so, calculate the spillover amount that should be transferred to the next row.
 Update the Rows: Adjust the 'Used' and 'Available' columns based on the calculated spillover.
Use the "Sort Rows" tile to sort by 'Date Authorized' (ascending). Add formula like 
RUNNING_SUM('Used')
A formula for 'Spillover'. Something like 
CASE WHEN Running Total > Authorized THEN Running Total  Authorized ELSE 0 END
Add a formula for a new " Capped Usage" column that caps usage 
CASE WHEN Running Total > Authorized THEN Authorized ELSE Used END
And a formula tile for new available 
Authorized  Capped Used
You might also need more spillover rows. If you can't get Magic ETL to do it properly, you might try some kind of recursion dataflow.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0  Hmmm. Maybe something like…
Answers

 Hmmm. Maybe something like…
Load and Sort Data: ensure that your data is sorted by the 'Date Authorized' column to process the entries in the correct order.
 Add a Running Total Column: Create a running total for the 'Used' column to keep track of the cumulative usage.
 Calculate the Spillover: For each row, determine if the running total exceeds the 'Authorized' amount, and if so, calculate the spillover amount that should be transferred to the next row.
 Update the Rows: Adjust the 'Used' and 'Available' columns based on the calculated spillover.
Use the "Sort Rows" tile to sort by 'Date Authorized' (ascending). Add formula like 
RUNNING_SUM('Used')
A formula for 'Spillover'. Something like 
CASE WHEN Running Total > Authorized THEN Running Total  Authorized ELSE 0 END
Add a formula for a new " Capped Usage" column that caps usage 
CASE WHEN Running Total > Authorized THEN Authorized ELSE Used END
And a formula tile for new available 
Authorized  Capped Used
You might also need more spillover rows. If you can't get Magic ETL to do it properly, you might try some kind of recursion dataflow.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0  Hmmm. Maybe something like…
Categories
 All Categories
 1.8K Product Ideas
 1.8K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 299 Workbench
 6 Cloud Amplifier
 8 Federated
 2.9K Transform
 100 SQL DataFlows
 616 Datasets
 2.2K Magic ETL
 3.8K Visualize
 2.5K Charting
 730 Beast Mode
 55 App Studio
 40 Variables
 682 Automate
 175 Apps
 451 APIs & Domo Developer
 46 Workflows
 10 DomoAI
 34 Predict
 14 Jupyter Workspaces
 20 R & Python Tiles
 394 Distribute
 113 Domo Everywhere
 275 Scheduled Reports
 6 Software Integrations
 121 Manage
 118 Governance & Security
 8 Domo Community Gallery
 38 Product Releases
 10 Domo University
 5.4K Community Forums
 40 Getting Started
 30 Community Member Introductions
 108 Community Announcements
 4.8K Archive