Complex multi row manipulation

[Trailduck]

Hi!

I have a use case where I'm generating inventory aging, and I need to do some complex multi row manipulation I cannot figure out.

What I'm trying to do is to split values from quantity into new rows every time there is a new delivery. So let's take a practical example from the screenshot below.

At 2005-06-05 I had 113 quantity in stock, and 2005-06-06 I got a resupply and now I have 189 in stock. What I want to do here is to take the delta between those two days into a new row so I can continue calculating the stock age for that specific stock quantity. In this case it would be 76. I then want every single minus in quantity going forward to substract from the 113 before starting to substract from the new 76. 

Any smart people that can help me here?

[caltang]

I am assuming your first three columns are unique...

Use a Multi-Row Tool and Group by the three columns you have first.

Second, for a Delta to be present, what you can do is the following:

IF quantity = [quantity + 1]

THEN 0

ELSE [quantity + 1] - quantity

ENDIF

Choose the data type to Int16 as well.

Try it and see.

Hope it helps.

-Cal

[Trailduck]

Maybe I didn't explain correctly. I've also added some more columns to make it understandable.

Every time there is a new delivery, I want the salesAmount to be substracted from the previous stockQuantity until it reaches 0 before starting to substract from the new stockQuantity. So it should substract all salesAmount from 113 until it reaches 0, then it should start substracting from 76.

[Yoshiro_Fujimori]

Hi @Trailduck ,

I assume you use your stock FIFO (First In First Out), and you want to know the number of remaining items by each entry date.

If so, I would assign ID to each item in the stock, and add the date when it was input and output.

Input

Put only the date when there is a movement (In or Out).

Output

Workflow

I hope this works for your requirement.

[Trailduck]

Getting closer to something here, but what I'm trying to do is to calculate the age for the stock.

[Trailduck]

Getting closer to something here, but what I'm trying to do is to calculate the age for the stock.

[Yoshiro_Fujimori · Accepted solution]

@Trailduck ,

It depends on the definition of "age".

If you want to get the days from the In-Date to a specific date, you can just add a formula tool as below.

Workflow added

Output

If you have any other definition, you would need different approach.

But as long as you have the data for each item, it should not be difficult.