Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
spetushi
Creator
Creator

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

May be this

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

spetushi
Creator
Creator
Author

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

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
Creator
Creator
Author

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
Creator
Creator
Author

Done and sent you a message.

qlikdelqbz
Contributor III
Contributor III

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
Contributor III
Contributor III

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