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.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 286 Workbench
 4 Cloud Amplifier
 7 Federated
 2.8K Transform
 92 SQL DataFlows
 584 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 653 Beast Mode
 37 App Studio
 33 Variables
 638 Automate
 163 Apps
 432 APIs & Domo Developer
 40 Workflows
 3 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 104 Manage
 101 Governance & Security
 6 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 93 Community Announcements
 4.8K Archive