Beg Balance + Activity + Units = Ending Balance, Ending Units Calculations

I saw a more simple post related to this, but my table is a little more complicated as it has a few more components then just Beginning balance + activity = ending balance.

I've attached a workbook with some sample data and have color coded the cells based on where the information is coming from. Long story short, some of the information in my table is coming from an underlying report and the other information in the table is recalculated amounts based formulas and numbers from the report (i.e. I've added the yellow highlighted columns to my original report).

I'm having trouble calculating "Units Bought (Sold)", "Ending Units", and "Ending Units Value". The simple formulas for these are:

Units Bought (Sold) = [Current Row Activity] / [Row-1 Ending Units Value]

Ending Units = [Current Row Units Bought (Sold)] + [Row-1 Ending Units]

Ending Units Value = [Current Row Ending Value] / [Current Row Ending Units]

I've tried the multi row formula tool, but I have to repeat the tool each time as the units bought (sold) and ending units are dependent on each other. Basically, if I were to do this in excel, I would use the above formulas for one row and then drag those formulas down to the remaining rows. The piece that is throwing me off is how Alteryx calculates the formula for each row for the entire column, which makes the formulas above not work.The attached file includes the formulas I would use if I was trying to do this in excel.

Thanks in advance for the help!

Alteryx Activity Table Example.xlsx

Tips and Tricks

Common Use Cases

Transformation

Accepted answers

tcroberts

In this case you'll only need 1 regular workflow. This regular workflow will contain the Batch Macro, which in turn will contain the Iterative Macro. Other than that it sounds like you've got the gist of it though.

Your batch macro can be as simple as:

The output here is the output from the previous solution I posted, but each entity will be computed with only the same group, and at the end all of the outputs will be unioned back together. You'll need to split these up yourself afterwards to get your tables back.

In your outer 'regular' workflow, youll want to union together all your data into a single stream, making sure that there is the "Entity" field to distinguish where they came from. You can then GroupBy that field to get all the choices, and pass that to the Control Input (upside down question mark anchor) of your Batch Macro:

The lower input anchor will just be your regular data stream.

Let me know if you need more clarification,

Cheers!

All comments

tcroberts

I've approached this using an Iterative Macro which I've attached below:

Essentially, I iterate through, filling in a single row at a time. The loop breaks (stops sending values to iterative output) when there are no Null values left in the Ending Unit Value field.

Let me know if you need some clarification,

Cheers!

formulasreliantoneachother.yxmc

adamscody91

Thank you for the response! That worked based on my previously attached example table. However, I'm hoping you can help me by adjusting your workflow a little bit to be more dynamic based on the input file/table.

I have attached an updated table for your reference.

The only change between the previous attachment and the current attachment is the addition of the "Entity" column and the original table being duplicated 3 times (with some minor # changes within the tables). Basically, the table originally provided was for one entity, but I need to repeat this process for multiple entities. All previous formulas should be the same, but the formulas need to be grouped by entity as the data for entity A needs to remain separate from the data for entity B. To make it harder, I need to run the workflow for a variable number of entities each time I run it. The number of entities I need to run the workflow for is dependent on the number of entities in the original report I receive. For example, the report I receive on Monday will have data for 20 entities and therefore I need to create 20 tables, but the report I receive on Friday could have data for 30 entities and therefore I need to create 30 tables.

Said differently, I'm trying to make your Iterative Macro run the formulas for each row for each entity and the number of entities may not be the same each time I need to run the workflow. I'm guessing that I need to do something to update the Macro Input tool or create a batch macro to the run the process through for each entity, but I'm struggling on how to achieve these results.

Please let me know if additional information/clarification is needed.

Thanks!

Alteryx Activity Table Example_Multiple.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703