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

[adamscody91]

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!

[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!

[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!

[tcroberts]

Try wrapping this macro inside a Batch Macro. You can set the "GroupBy Field" to Entity, and then within the Batch Macro, put the iterative macro. You can turn a workflow into a Batch Macro by going to the Configuration Pane -> Workflow -> Type -> Macro -> Batch Macro.

The workflow should go something like this:

1) Read in all your data, create 1 table with the Entity column indicating the different inputs.

2) Pass this stream to a batch macro, with the GroupBy Fields set to Entity

3) The Macro Input here should be passed to the Iterative Macro in my previous answer. When this is run, it will pass one entity at a time to this iterative macro. Pass the output of this macro to the Batch Macro Output

4) The output of the Batch Macro should be a table containing the computed values done on one entity at a time, and then unioned together. You can then output as desired, if needed, you can use the Entity Field to Group data in an Output Tool, letting you create separate Excel sheets or files for each Entity

Let me know if you need clarification on how to set this up.

Cheers!

[adamscody91]

I understand conceptually what you are saying, but I'm having a little trouble figuring out how to exactly achieve this alteryx. If possible could post a picture or sample workflow of what this would look like in Alteryx?

There is some minor data cleansing/formulas that I add to the initial report before it looks like the table that I had previously uploaded. So would my workflow look like the following?

1) Input report and clean data / run formulas then output report

2) Output from #1 would be batch macro input and then the only step in the batch macro workflow would be the iterative macro followed by macro output

3) Output from #2 would then input to final workflow where I add a few more formulas / clean up the data and then output final product

Does this process result in me requiring to have 4 workflows? Initial regular workflow, batch macro workflow, iterative macro workflow, and then final regular workflow? If correct, is it possible to consolidate this at all?

Appreciate the help!

[tcroberts · Accepted solution]

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!

[adamscody91]

That worked perfectly!

Thank you so much for the help.

Really appreciate it!

[tcroberts]

Glad to hear I could help!