Save local variable within expression

[Flamingi]

I have a formula that is taking quite some time to evaluate. Based on the result I want to display a different message. Is there a way to store the result in a local variable to use within the formula? something like this

result = $(long_calculation(par1, par2))
if (result < 0.5) 
    then do option 1
else if (result < 1)
    then do option 2
else if (result < 1.5)
    then do option 3
...

currently i call $(long_calculation) in every if and performance is really bad. The result of the calculation depends on the current selection so it can not be precalculated and stored unfortunately. Is there 

[marcus_sommer · Accepted solution]

If your calculation is globally then you could use = at the beginning of the variable what meant that the result of the calculation is assigned to it. Each call of such variable would just take this result and not calculate the variable again and again.

If your calculation is depending on the used context then the above mentioned approach won't work but you could reduce the number of calculations with an approach like this:

pick(match(floor(result, 0.5), 0, 0.5, ...), 'option1', option2', ...)

Beside this the calculation itself might be optimized in any way and/or some adjustements within the datamodel to reduce the efforts to create the virtual table on which the aggregations will be performed.

- Marcus

[Flamingi]

I have tried to use your suggestion, but what i implemented is really ugly, because I can't use the floor function.

My long calculation returns how much % of the budget we achieved (sales/budget -> 50% = 0.50). The actual limits are

• <95% -> opt1
• >=95% and <97.5% -> opt2
• >= 97.5% and <= 102.5% -> opt3
• > 102.5% and <= 105% -> opt4
• > 105% -> opt5

I have used this code, but that's probably not how it is intended

pick(
    match(mid($(OPTION_STRING), round($(long_calculation) * 1000), 1), 'a', 'b', 'c', 'd', ''),
    opt1, opt2, opt3, opt4, opt5
)

OPTION_STRING = repeat('a', 949) & repeat('b', 25) & repeat('c', 51) & repeat('d', 25)

The idea behind this is the following:

• Make the option string according to the limits
• Multiply the result with 1000 and round it to get an integer (96.5% --> 965). Call this number x
• Get the xth character of the option string
• Now use the match pick combo you proposed

It does work, it is quite fast because the long_calculation is only used 1 time, but it is suuuuuper ugly. Does anyone know a better solution?

[marcus_sommer]

The mentioned rounding-approach could be still applied because you could set multiple matches and returns within the pick(match()). This means you could set it in this way:

pick(match(Expression,
   0,25,50,75,100,125,150,175,....,975, 1000, ....),
   o1,o1,o1,o1,o1,o1,o1,o1,o1,..., o1,o1,o1,o1,...,o2,o3,...)

The idea to transform the floatings into integers is often helpful by such an approach. By relative many items within the pick(match()) this is rather ugly too, but it could be shortened by using some kind of mapping-table (not further associated with the datamodel) for it (often quite easily in Excel with copy&paste created). And then your expression might be look like:

pick(match(Expression,
   $(=concat(MatchValues, ',')),
   $(=concat(ReturnValues, ',')))

 If the ReturnValues don't contain just valid numbers else any kind of strings it will need some further adjustment to ensure that the adhoc-variable returned a valid string-list like you would writte it manually, for example:

   '$(=concat(ReturnValues, chr(39) & ',' & chr(39))'

- Marcus