topic Re: Complex 'if" formula in QlikView

Complex 'if" formula

Thu, 06 Sep 2012 08:52:36 GMT

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

Re: Complex 'if" formula

thomas_skariah — Thu, 06 Sep 2012 09:15:48 GMT

Hi Nayan,

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

Regards,

Tom

Re: Complex 'if" formula

Thu, 06 Sep 2012 09:19:09 GMT

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

Re: Complex 'if" formula

Thu, 06 Sep 2012 09:47:04 GMT

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 code	Average	Forecast Oct-2012	Nov-2012	Dec-2012	Jan-2013	SOH	Stock Holding	Ideal Stock Holding	Maximum Stock Holding
A	56	277	1,076	554	607	754	8.9	1,353	2,515
B	3	56	35	13	50	213	42.6	91	154
C	2,301	328	2,552	1,146	2,517	2,766	0.6	2,880	6,543
D	2,589	1,758	876	4,129	-4	4,664	0.8	2,633	6,758
E	891	2,105	747	1,417	2,899	1,519	1.6	2,852	7,168
F	170	436	1,533	262	1,417	276	5.2	1,969	3,648
G	383	396	757	400	581	1,433	4.0	1,153	2,134
H	1,398	593	2,045	2,941	1,223	3,878	3.1	2,638	6,802
I	2,593	1,789	3,989	3,456	2,237	6,447	2.3	5,777	11,471
J	3,949	6,420	9,771	4,721	5,243	4,251	0.7	16,191	26,155
K	1,352	2,420	2,464	1,645	0	1,480	0.8	4,884	6,529
Total	15,685	16,577	25,845	20,686	16,768	27,682	1.2	42,422	79,875

Re: Complex 'if" formula

Thu, 06 Sep 2012 09:53:44 GMT

Hi Vicky

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

kind regards

Nayan

Re: Complex 'if" formula

Thu, 06 Sep 2012 10:17:01 GMT

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

Re: Complex 'if" formula

thomas_skariah — Thu, 06 Sep 2012 10:23:05 GMT

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

Re: Complex 'if" formula

Thu, 06 Sep 2012 10:51:56 GMT

Hi Vicky and Tom

Both your methods work. Thank you. Much appreciated.

kind regards

Nayan