Performance in expression

[Anonymous]

Hi Guys,

I have an performance issue with an expression in the calculation function:

if(^Exp_Curr='USD', sum(Omzet_USD)
,if(^Exp_Curr='GBP',sum(Omzet_GBP)
,sum(Omzet_EUR)))

^Exp_Curr is a picking field for currency.

This if construction causes performance issue's, I can see multi blogs around this problem.

What's the best way for solving this calculation in a different way?

Regards

Robert

[Peter_Cammaert · Accepted solution]

I can't imagine why this expression would cause performance problems (if you're not placing the IF() inside the Sum() function call), but you can always try defining a variable called for example vCurrencySelector as

=^Exp_Curr

and change your expression into something like:

=sum(Omzet_$(vCurrencySelector))

Ignore the red wiggly lines in the expression eduitor; the syntax checker doesn't understand what you mean, but it will work.

Peter

[miguelbraga]

Hey there,

Data Architect here to help on solving this issue

Usually on of the best practices in developing a dashboard is to bring the most complexity to the script and avoid using a calculated Dimension.

What you can do to solve this is to do something like this in your script:

Table:

LOAD *,

          sum(Omzet_USD)*Flag_USD as Currency_USD,

          sum(Omzet_GBP)*Flag_GBP as Currency_GBP,

          sum(Omzet_EUR)*Flag_EUR as Currency_EUR;

LOAD *,

          if([^Exp_Curr] = 'USD', 1, 0) as Flag_USD,

          if([^Exp_Curr] = 'GBP', 1, 0) as Flag_GBP,

          if([^Exp_Curr] = 'EUR', 1, 0) as Flag_EUR;

LOAD *

FROM [your connection to your database goes here];

Hope this helps

Best regards,

D.A. MB

[Anonymous]

Hi Peter,

Thanks,. That worked fine!

regards,

Robert

[Anonymous]

Hi Peter,

Thanks for the answer. It works when I put the calculation sum(Omzet_$(vCurrencySelector)) in the chart expression.

But i'm using a variable as expression ($(vSalesCurrency)) and this variable is already loaded in the script using the Include statement so I can't be calculated dynamically.

It there a way to calculated the expression with the included statement?

Thanks

Robert

[Peter_Cammaert]

Are you experiencing $-sign expansion at the wrong moment, e.g. too early? That can be handled as well (there are many techniques available, all of them delaying $-sign expansion) but to show you how it would work in your environment, we need to know how you define these variables in your script. Do you load them from from an external source? Do you assemble them with SRT/LET string assignments?

Best,

Peter

[Anonymous]

Hi Peter,

Thank for your reply.

Yes that's correct. It's calculated too early from external source (excel).

And the string is the LET assignment

RObert

[Peter_Cammaert]

You should delay the $-substitution by replacing the $-sign in your Excel source by something else (a pound sign # for example) and restoring the $-signs in your script immediately before creating the expression variable using a Replace() function call. If you do not want to change your Excel source, use two Replace() calls to translate $-signs into something else during first load, and to restore $-signs during final vairable assignment.

Whatever you do inbetween these two steps won't affect the part that is to be substituted during expression evaluation in your objects.

If you want an example, post the script part that handles Excel input and variable creation.

Peter