The thing is I am finding solution for the below case. Here I wish to find the that orderQ is spread across to how many months. E.g. > for the month of February orderQ 55 compares it with Fvalue and I should get the figure in new column as 20 (55-35) > Again it compares it with next row since the result is positive (20-15) and I should get the figure in new column as 5 > again it compares it with next row since the result is positive (5-2) as 3 and so on. --> Comparison should not go beyond December --> In case of comparison coming out to be negative, this logic should not proceed Problem _ Code1MonthFvalueCode2orderQabc123January49 February35abc12355 March15 April2 May16abc12315 June10abc12334 July12 August10abc12310 September21 October32abc12345 November44 December16 20xyz456January23xyz45645 February16 March10xyz45667 April12xyz45667 May10 June21xyz45656 July32 August35xyz45634 September15xyz45623 October23 November12 December10xyz45623 Expected Result Code1MonthFvalueCode2orderQNewFieldExplanationabc123January49 February35abc1235520(55-35) March15 5(20-15) April2 3(5-2) May16abc12315-1(15-16) June10abc1233424(34-10) July12 12(24-12) August10abc1231012(12+10-10) September21 -9(12-21) October32abc1234513(45-32) November44 -31(13-44) December16 204(20-16)xyz456January23xyz45645 February16 March10xyz45667 April12xyz45667 May10 June21xyz45656 July32 August35xyz45634 September15xyz45623 October23 November12 December10xyz45623 Thanks in advance FVT_Test.xlsx

How to create a workflow for the below case (inventory adjustment)

The thing is I am finding solution for the below case. Here I wish to find the that orderQ is spread across to how many months.

E.g.

> for the month of February orderQ 55 compares it with Fvalue and I should get the figure in new column as 20 (55-35)

> Again it compares it with next row since the result is positive (20-15) and I should get the figure in new column as 5

> again it compares it with next row since the result is positive (5-2) as 3 and so on.

--> Comparison should not go beyond December

--> In case of comparison coming out to be negative, this logic should not proceed

Problem _

Code1	Month	Fvalue	Code2	orderQ
abc123	January	49
	February	35	abc123	55
	March	15
	April	2
	May	16	abc123	15
	June	10	abc123	34
	July	12
	August	10	abc123	10
	September	21
	October	32	abc123	45
	November	44
	December	16		20
xyz456	January	23	xyz456	45
	February	16
	March	10	xyz456	67
	April	12	xyz456	67
	May	10
	June	21	xyz456	56
	July	32
	August	35	xyz456	34
	September	15	xyz456	23
	October	23
	November	12
	December	10	xyz456	23

Expected Result

Code1	Month	Fvalue	Code2	orderQ	NewField	Explanation
abc123	January	49
	February	35	abc123	55	20	(55-35)
	March	15			5	(20-15)
	April	2			3	(5-2)
	May	16	abc123	15	-1	(15-16)
	June	10	abc123	34	24	(34-10)
	July	12			12	(24-12)
	August	10	abc123	10	12	(12+10-10)
	September	21			-9	(12-21)
	October	32	abc123	45	13	(45-32)
	November	44			-31	(13-44)
	December	16		20	4	(20-16)
xyz456	January	23	xyz456	45
	February	16
	March	10	xyz456	67
	April	12	xyz456	67
	May	10
	June	21	xyz456	56
	July	32
	August	35	xyz456	34
	September	15	xyz456	23
	October	23
	November	12
	December	10	xyz456	23

Thanks in advance

FVT_Test.xlsx

Developer

Accepted answers

DavidP

In that case, this formula will do that

inventory adjustment.yxmd

All comments

DavidP

I nearly get the expected result, except for August. I don't understand the logic for August. I built the logic to only look at the 1st 12 rows, but if you wanted to repeat the same logic for xyz4546, this can easily be done with grouping in the multi-row formula.

If you can explain the logic for August, I can adjust the workflow for you.

inventory adjustment.yxmd

grossal

Hi @DavidP,

he posted the problem here with some additional information, but he hasn't answered the latest question. I stumbled across the same thing as you did.

hishashvat

@DavidP @grossal

I am extremely sorry for missing out on the part of the information in my problem. I apologize for the same.

Actually I was looking out for the vales of Ordered quantity for the spread of over 3 months only. Therefore I stopped at the month of April.

But if above logic is difficult to incorporate in the workflow and will make it complex then it will be fine if all the entries are adjusted likewise

Corrected table as per the discussion (May month adjusted )

Code1	Month	Demand	Code2	Ordered Q	Inventory	Explanation
abc123	January	49
	February	35	abc123	55	20	(55-35)
	March	15			5	(20-15)
	April	2			3	(5-2)
	May	16	abc123	15	2	(15-16+3)
	June	10	abc123	34	26	(34+2-10)
	July	12			14	(26-12)
	August	10	abc123	10	14	(14+10-10)
	September	21			-7	(14-21)
	October	32	abc123	45	6	(-7+45-32)
	November	44			-38	(6-44)
	December	16		20	-34	(-38+20-16)

Inventory = Inventory previous month + Ordered quantity - Demand

Quick Links

Unanswered
Community Events
Groups

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335