Expression optimization

[#1]

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

[jerem1234]

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]

Try

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

[rwunderlich]

" 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

[#5]

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]

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)

[simondachstr]

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

[SergeyMak]

did you try it?

[simondachstr]

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

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

[simondachstr]

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.