Skip to main content
Announcements
Jan 15, Trends 2025! Get expert guidance to thrive post-AI with After AI: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression optimization

Hello everyone

I'm  trying to optimize this expression

if (PER=0,
      IF(IND=0,sum(Value*coef),

           sum(Value*IND*coef)),

                     IF(IND=0,sum(Value*coef*PER),

                                    sum(Value*IND*coef*PER))) 

the thing is it depends on the users selection so can't optimize it in the script, and at the same time pick (match) doesn't work because for example PER can aither be 0 or (0 or 1).same for IND.

Any idea can be helpful,

Thanks in advance

15 Replies
jerem1234
Specialist II

Maybe you can combine the two dimensions like:

pick(match(PER& '|' & IND, '0|0', '0|0 or 1', '0 or 1|0', '0 or 1|0 or 1'), sum(Value*coef), sum(Value*IND*coef), sum(Value*coef*PER), sum(Value*IND*coef*PER))

Hope this helps!

EDIT: IF you meant just 0 or 1 then:

pick(match(PER& '|' & IND, '0|0', '0|1', '1|0', '1|1'), sum(Value*coef), sum(Value*IND*coef), sum(Value*coef*PER), sum(Value*IND*coef*PER))

martynlloyd
Partner - Creator III

Try

 

sum(Value*coef) * IF(IND=0, 1, IND) * IF(PER=0, 1, PER)



rwunderlich
Partner Ambassador/MVP

" because for example PER can aither be 0 or (0 or 1).same for IND."

If the values are only 0 or 1, what's the point in multiplying them? Or are there other values possible?

-Rob

Not applicable
Author

the fiel PER depending on the user's selection can aither be equal to 0 , or could be both 0 and 1 , in other words

if PER is not equal to 0 , than  sum(PER)>=1 . same concept for the FIELD IND

SergeyMak
Partner Ambassador

Try this


Sum({$<PER={0},IND={0}>}Value*coef)

+

Sum({$<PER=-{0},IND={0}>}Value*coef*PER)

+

Sum({$<PER={0},IND=-{0}>}Value*coef*IND)

+

Sum({$<PER=-{0},IND=-{0}>}Value*coef*IND*PER)


Regards,
Sergey
simondachstr
Luminary Alumni

This will be static and won't reflect possible selections.

SergeyMak
Partner Ambassador

did you try it?

Regards,
Sergey
simondachstr
Luminary Alumni

Have a look at maybe using SubStringCount() function to imitate select case:

http://community.qlik.com/docs/DOC-5841

simondachstr
Luminary Alumni

I must say I didn't see the "-" before the "{" - it looked like an extension -{

Edit:

But even so, in your expression e.g. Sum({$<PER={0},IND={0}>}Value*coef) will always calculate even if selected PER & IND are not 0.

In meriem's expression, if PER & IND are unequal 0, sum(Value*IND*coef*PER) only should be calculated.