SEVERAL IF AND FORMULA IN A SCRIPT

[#1]

Dear All

[8-)]

I'm trying without success to set up a formula in a QV script with several functions IF(AND... imbricated but it does not work.

IF(([TOT_MAR09]<=[TOT_FEB09]*(1-0.5))AND([TOT_FEB09]<=[TOT_JAN09]*(1-0.5))AND([TOT_JAN09]<=[TOT_DEC08]*(1-0.5)),'YES',

IF(([TOT_APR09]<=[TOT_MAR09]*(1-0.5))AND([TOT_MAR09]<=[TOT_FEB09]*(1-0.5))AND([TOT_FEB09]<=[TOT_JAN09]*(1-0.5)),' YES ',

IF(([TOT_MAY09]<=[TOT_APR09]*(1-0.5))AND([TOT_APR09]<=[TOT_MAR09]*(1-0.5))AND([TOT_MAR09]<=[TOT_FEB09]*(1-0.5)),'YES',

IF(([TOT_JUN09]<=[TOT_MAY09]*(1-0.5))AND([TOT_MAY09]<=[TOT_APR09]*(1-0.5))AND([TOT_APR09]<=[TOT_MAR09]*(1-0.5)),'YES',

IF(([TOT_JUL09]<=[TOT_JUN09]*(1-0.5))AND([TOT_JUN09]<=[TOT_MAY09]*(1-0.5))AND([TOT_MAY09]<=[TOT_APR09]*(1-0.5)),'YES',

'NO'))))))

as [Status]

Many thanks in advance for your help

Regards

[#2]

Is that synax error you are getting?

Or do you mean, you are not getting expected result?

[#3]

This is a syntax error missing/misplaced

Thanks

[#4]

I just tried this in my code, and it works well. Replace the (1)s and( -1)s with your required fields (carefully).

IF(((-1)<=(1)*(1-0.5))AND((1)<=(1)*(1-0.5))AND((-1)<=(1)*(1-0.5)),'YES',
IF(((-1)<=(1)*(1-0.5))AND((1)<=(1)*(1-0.5))AND((-1)<=(1)*(1-0.5)),'YES',
IF(((-1)<=(1)*(1-0.5))AND((1)<=(1)*(1-0.5))AND((-1)<=(1)*(1-0.5)),'YES',
IF(((-1)<=(1)*(1-0.5))AND((1)<=(1)*(1-0.5))AND((-1)<=(1)*(1-0.5)),'YES',
IF(((-1)<=(1)*(1-0.5))AND((1)<=(1)*(1-0.5))AND((-1)<=(1)*(1-0.5)),'YES'
,'NO'))))) as Status

[#5]

Thanks a lot there is no longer any syntax error.

But my issue is now, It returns only "yes" value with the full data.

Many thanks again

[biester]

Hi Edith,

sorry to interfere, but: I think that (besides that the result ain't what you expected) this code is hardly maintainable (as I suppose that TOT_APR09 stands for "Totals April in the year 2009" you will have to rewrite the whole matter 2010, and again 2011 ....). So if it's not ad hoc code (but even if it IS) you should overthink it. I don't know what's behind it, actually I ain't got the nerves to go through it (and actually should perhaps shut up therefore ;-). Nevertheless, perhaps you can setup a logical matrix, work with flags or whatever. I know I'm always a little spiteful when it comes to similar constructs, but those "If"s are really born for the "Schaffott" (scaffold) - sorry for the joke, couldn't suppress it. And sorry again for interfering, but I've become allergical to those constructs (my colleague uses such monsters twice a day :-(( .

Rgds,
Joachim