Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Peter,
Thanks,. That worked fine!
regards,
Robert
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
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
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
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