6 Replies Latest reply: Jan 11, 2018 11:05 AM by Sokol Petushi

# Set Analysis with nested IF Statements and Num() Formatting

Hello,

I would like to convert the following if statement to the corresponding set analysis expression hoping in some performance improvements:

= If(Site = 'North America' AND Sum(RatioFX) > 0 AND Sum(RatioFX) < 1, Num(Sum(RatioFX),'#0.00%'), If(Sum(RatioFX) = 0, 'NA', '100%'))

I have converted it as follows, but would like to identify a more optimized way to write this as set analysis as I am still using 'IF's so I can format the result:

= If(Sum({<Site={'North America'}>}RatioFx) > 0 AND Sum({<Site={'North America'}>}RatioFX) < 1, Num(Sum({<Site={''North America''}>}RatioFX),'#0.00%'), If(Sum({<Site={''North America''}>}RatioFX) = 1,'100%','NA'))

Thank you,

Sokol

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

May be this

If(Sum({<Site={'North America'}>}RatioFx) = 0, 'N/A', Num(Sum({<Site={''North America''}>}RatioFX), '#0.00%'))

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

Thank you for the prompt suggestion Sunny! I would like to avoid showing the decimals '.00' when the result is 100% for visual affect. Only need the two decimals when the result is > 0 and < 100

Thank you,

Sokol

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

May be this

If(Sum({<Site={'North America'}>}RatioFx) = 0, 'N/A', Num(Sum({<Site={''North America''}>}RatioFX), If(Sum({<Site={''North America''}>}RatioFX) = 1, '#.%', '#0.00%')))

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

Thank you Sunny, this works!

What do you do for a leaving? How can I reach you privately?

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

I have added you as a connection... once you accept, you can send me a private message on Qlik Community

• ###### Re: Set Analysis with nested IF Statements and Num() Formatting

Done and sent you a message.