Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Complex 'if" formula

Hi

I have the following complex "if" formula below which does not produce a complete result or only produce a result for one of the conditions.  And if I apply one of the 4 conditions and comment the others out, then the formula works individually.  Please can you tell me where i am going wrong

if(SOH< [Ideal Stock Holding],if([Stock Holding]<2,'Low Stock',

if(SOH> [Maximum Stock Holding],if([Stock Holding]>8,'Overstock',

if(SOH< [Ideal Stock Holding],if([Stock Holding]>8,'Consider',

if(SOH> [Maximum Stock Holding],if([Stock Holding]<3,'Consider',''))))))))

Thank you

kind regards

Nayan

1 Solution

Accepted Solutions
Not applicable
Author

I have change the example file with your data. My expression is working if you set a comma after the second "Consider". I have forgotten it first and changed my post yet.

Regards

Vicky

View solution in original post

7 Replies
thomas_skariah
Creator III
Creator III

Hi Nayan,

Can you explain the content in SOH,Ideal Stock Holding and Maximum Stock Holding or provide a sample xl file.

Regards,

Tom

Not applicable
Author

HI Nayan,

Maybe you have to sort your expression a little bit,

if(SOH< [Ideal Stock Holding],

     if([Stock Holding]<2,'Low Stock',

     if([Stock Holding]>8,'Consider', '')),

if(SOH> [Maximum Stock Holding],

     if([Stock Holding]<3,'Consider',

     if([Stock Holding]>8,'Overstock', ''))

, ''))

So the expression first looking at all the Ideal Stock Holding and if it finds nothing there it is going through the Maximum Stock Holding.

Regards

Not applicable
Author

Hi Tom

SOH - Is my current Stock on hand

Ideal Stock Holding - is the Ideal Qty on Hand we should keep which is the sum of the next 2 months sales quantity

Maximium Stock holding - this the maximum qty on hand that we should keep which is the sum of the next 4 months sales qunatity

Stock Holding -  which is current Stock on hand divided by my Average  (which is average last 6 months sales)

below is the table

Item codeAverageForecast Oct-2012Nov-2012Dec-2012Jan-2013SOHStock HoldingIdeal Stock HoldingMaximum Stock Holding
A562771,0765546077548.91,3532,515
B35635135021342.691154
C2,3013282,5521,1462,5172,7660.62,8806,543
D2,5891,7588764,129-44,6640.82,6336,758
E8912,1057471,4172,8991,5191.62,8527,168
F1704361,5332621,4172765.21,9693,648
G3833967574005811,4334.01,1532,134
H1,3985932,0452,9411,2233,8783.12,6386,802
I2,5931,7893,9893,4562,2376,4472.35,77711,471
J3,9496,4209,7714,7215,2434,2510.716,19126,155
K1,3522,4202,4641,64501,4800.84,8846,529
Total15,68516,57725,84520,68616,76827,6821.242,42279,875
Not applicable
Author

Hi Vicky

Thank you .  Will apply your method and get back to you.

kind regards

Nayan

Not applicable
Author

I have change the example file with your data. My expression is working if you set a comma after the second "Consider". I have forgotten it first and changed my post yet.

Regards

Vicky

thomas_skariah
Creator III
Creator III

Hi Nayan,

Try this code

if(SOH<[Ideal Stock Holding] and [Stock Holding]<2,'Low Stock',
if(SOH<[Ideal Stock Holding] and [Stock Holding]>8,'Consider',
if(SOH>[Maximum Stock Holding] and [Stock Holding]<3,'Consider',
if(SOH>[Maximum Stock Holding] and [Stock Holding]>8,'Overstock')))) as Status

Not sure whether your requirement fullfill this code.

Regards,

Tom

Not applicable
Author

Hi Vicky and Tom

Both your methods work.  Thank you.  Much appreciated.

kind regards

Nayan