Running total spilling into a new column

Options
damen
damen Contributor
edited June 2024 in Magic ETL
image.png

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!

Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    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! **

Answers

  • ArborRose
    ArborRose Coach
    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! **

Categories