IF Statement

Hi,

I am looking for help with an if statement that is not working:

Planned Order:

IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders] - [Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]) < [Reorder point]) then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders] - [Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

The formulas that are adding and subtracting are not working within the IF statement and it is giving everything is below the Reorder Point which is not true.

Workflow

Accepted answers

BS_THE_ANALYST

@CM246 thanks for the sample fields but that was a miscommunication.

I'm suggesting a method for you to be able to debug this yourself in a coherent and quick way.

Might I suggest something like the following:

Break the IF statement up into several columns. This will allow you to determine which part(s) are causing the issue, right.

Hopefully that makes sense?

All the best,

All comments

Raj

@CM246

you can try this
IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) < [Reorder point]) then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

this should work

mark done if solved.

CM246

This has changed the outcome so it's definitely the placement of brackets that is the issue, however, it is still not working correctly.

The flow is too large to share however this is the calculation using the below and the figures:

IF(([916] + [1084]) - ([583]+[1600])) < [2000]) then ([2000 - ([916] + [1084]) - ([583]+[1600])) else [0] endif

As you can see, the if statement is > the Reorder point and should not calculate the second half which it is and it gives -2183 as the result

CM246

Ok, so I changed the brackets to this and it seems to work on the first half but the second half it is not: then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]))

it is taking the reorder point and minus each individual figure i.e. Reorder point] - ([Month 4 Inventory Projection] + [Sum_Month_4_Planned_Orders] + [Sum_Month_5_Open_PO]) - [Sum_Month_5_Requirements]

2000 - (2264 + 0 + 0) - 583

So in this case my answer should be 2000 (reorder point) - 1681 = 319 should be my planned order

I hope this isn't too confusing and makes sense...

Raj

@CM246
can the paste the formula you are using now
and the values of
[Month 2 Inventory Projection]
[Sum_Month_2_Planned_Orders]
[Sum_Month_3_Requirements]
[Sum_Month_3_Open_PO])
[Reorder point])

and required output will correct the formula

Raj

@CM246
updated

IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]) < [Reorder point]) then [Reorder point] - (([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

mark done if solved.

CM246

This also did not work - it is giving -1016 for Month 2 planned orders using below numbers:

IF(([916] + [1084]) - ([583]+[1600]) < [2000]) then [2000] - (([916] + [1084] +[1600]) - ([583])) else [0] endif

So it seems it is adding 2000 + 583 (Reorder point + requirements) and then minus everything else

BS_THE_ANALYST

@CM246 this seems awful to debug.

Can you create some columns which represent pieces of your if statement. Then you'll see where issues are in a quick fashion.

All the best,

CM246

This is a sample of the fields I am pulling in from SAP.

All planned orders are 0, however, I want to place a planned order where the previous month's Inventory projection + the previous month's planned order (generated from the formula I am discussing now) - current month requirements is less than reorder point.

test material alteryx.xlsx

BS_THE_ANALYST

@CM246 thanks for the sample fields but that was a miscommunication.

I'm suggesting a method for you to be able to debug this yourself in a coherent and quick way.

Might I suggest something like the following:

Break the IF statement up into several columns. This will allow you to determine which part(s) are causing the issue, right.

Hopefully that makes sense?

All the best,

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703