Set Analysis with nested IF Statements and Num() Formatting

[spetushi]

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

[sunny_talwar]

May be this

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

[spetushi]

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

[sunny_talwar · Accepted solution]

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%')))

[spetushi]

Thank you Sunny, this works!

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

[sunny_talwar]

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

[spetushi]

Done and sent you a message.

[qlikdelqbz]

Hi Sunny, it still does not work for me. My formula looks like this

If(
match(Fin, 'IQ')<>0,

num(Avg(
{
State4< FYear={'$(vStartYear)'}, nValue-={0}>
}
nValue)/100, '#,#%'),

Avg(
{
State4< FYear={'$(vStartYear)'}, nValue-={0}>
}
nValue)
)

Could you help? Thanks!

[qlikdelqbz]

And I am using Qlik Sense. Maybe it only works in QlikView?