- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
sum(Value*coef) * IF(IND=0, 1, IND) * IF(PER=0, 1, PER)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
" 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Sergey
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This will be static and won't reflect possible selections.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
did you try it?
Sergey
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have a look at maybe using SubStringCount() function to imitate select case:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »