topic Re: Complex Nested if Statement in QlikView

Complex Nested if Statement

Mon, 11 Feb 2013 12:37:14 GMT

Hi,

I need to calculate a value from the following expression. Depending on the value of 'Value' and 'Indicator Number', I need to show a percentage, ratio, average or rate per 10,000.

I have tried this expression, but there is an error :

=if(Value='5',num(sum(number)/sum(total),'#,###.00'),

if(Value='4',num(sum(Number)/sum(Total)*10000),'#,###.00'),

if([Indicator Number]='7' and Value='3',num(sum(Number)/sum(Total)-sum(Number),'#,###.00'),

if(([Indicator Number]='8' or [Indicator Number]='9') and Value='2',sum(Number)/(sum(Total)+sum(Number),'#,###.00%'),

if(Value='1',num(sum(Number),'#,###.00'),

if(Value='2',num(sum(Number)/sum(Total),'#,###.00%'),num(sum(Number)/sum(Total),'#,###.00%')))))))

Can anyone see where Im going wrong?

Thanks,

Fiorano.

Re: Complex Nested if Statement

alexandros17 — Mon, 11 Feb 2013 12:50:36 GMT

unbalanced parenthesis, according to me the last parenthesis must be deleted in:

if(Value='4',num(sum(Number)/sum(Total)*10000),'#,###.00'),

Then fields are case sensitive so number and Number are different fields, if this is correct ok otherwise you must specify the correct world

Hope it helps

Re: Complex Nested if Statement

Mon, 11 Feb 2013 13:37:19 GMT

Hi thanks for your reply.

Im having trouble working out with parenthesis to remove. Could you post the amended statement?

Thanks again for your help.

Fiorano

Edit : expression is now working correctly :

=if(Value='5',num(sum(Number)/sum(Total),'#.00'),

if(Value='4',num(sum(Number)/sum(Total)*10000,'#,###.00'),

if([Indicator Number]='7' and Value='3',sum(Number)/sum(Total)-sum(Number),

if([Indicator Number]='8' and Value='2', num(sum(Number)/sum(Total)+sum(Number),'#,###.00%'),

if([Indicator Number]='9' and Value='2', num(sum(Number)/sum(Total)+sum(Number),'#,###.00%'),

if(Value='1',sum(Number),

if(Value='2',num(sum(Number)/sum(Total),'#,###.00%'),num(sum(Number)/sum(Total),'#,###.00%')

)))))))