topic Re: Set Analysis with nested IF Statements and Num() Formatting in QlikView

Set Analysis with nested IF Statements and Num() Formatting

spetushi — Thu, 11 Jan 2018 14:55:06 GMT

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

sunny_talwar — Thu, 11 Jan 2018 14:57:59 GMT

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

spetushi — Thu, 11 Jan 2018 15:02:08 GMT

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

sunny_talwar — Thu, 11 Jan 2018 15:11:14 GMT

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

spetushi — Thu, 11 Jan 2018 15:37:38 GMT

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

sunny_talwar — Thu, 11 Jan 2018 15:38:52 GMT

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

spetushi — Thu, 11 Jan 2018 16:05:42 GMT

Done and sent you a message.

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

qlikdelqbz — Mon, 18 Mar 2019 15:49:18 GMT

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!

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

qlikdelqbz — Mon, 18 Mar 2019 15:51:21 GMT

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