How to use Do While with interactive variable

[CarolinaSacramento]

Hi guys,

First of all, thanks for taking some time to help me. The thing is, I have a VBA process that I wish to put in Alteryx, it is a combination of Do While and interactive variables. I'll try to explain what the code is doing:

I have a series of products wich I need to calculate if there is a risk of expiring, however I have more than one expire date for the same product, so I have to sum the days that each lot takes to sell.

Here's na example:

SKU | Expire date | Days to expire | Days to sell all | Tile_Num | Tile_SequenceNum

1111   2021-05-01     32 days               30 days              1                          1

1111   2021-06-01     42 days               20 days              1                          2

1112   2021-04-15     16 days               50 days              2                          1

1112   2021-04-30     31 days               50 days              2                          2

The risk of the Tile_Num = 1, Tile_SequenceNum = 2 to expire is (30 + 20) - 42. Using the item's forecast I'm able to estimate how many we will loose. 

So I have 2 problems to adapt this code:

1) How can I store the days that Tile_Num = 1, Tile_SequenceNum = 1 took to sell to calculate the risk of the Tile_Num = 1, Tile_SequenceNum = 2?

2) How can I create a interactive variable to know when to change for the next Tile_Num?

Thank you all!

[Luke_C]

Hi @CarolinaSacramento 

I hope I'm understanding this correctly, but I believe the multi-row formula tool or running total are what you're looking for. I created a formula that will calculate the total amount of days to sell, and both tools allow a 'group by' where you can select SKU to make sure the total resets itself for the next item. 

Hopefully this helps