Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to load variables in the load editor. The variables include set analysis expressions.
Example:
Let vKPI_Budget = SUM({$<MonthNum ={'>=$(=vFMonth)<=$(=vTMonth)'}, Year={"$(=vYear)"}>}[Budget] );
How can I load this in the Data Load editor? It gives issues with the {, '' and "".
I prefer not doing it via an external excel with all variables, I know that works.
Try this:
LET vKPI_Budget = 'Sum({$' & '<MonthNum = {'>=$' & '(=vFMonth)<=$' & '(=vTMonth)'}, Year={"$' & '(=vYear)"}>}[Budget])';
Try this:
LET vKPI_Budget = 'Sum({$' & '<MonthNum = {'>=$' & '(=vFMonth)<=$' & '(=vTMonth)'}, Year={"$' & '(=vYear)"}>}[Budget])';
This might even work without the first break, but I am not 100% sure
LET vKPI_Budget = 'Sum({$<MonthNum = {'>=$' & '(=vFMonth)<=$' & '(=vTMonth)'}, Year={"$' & '(=vYear)"}>}[Budget])';
After I replaced the single quotes by the double quotes and used your technique to break it apart... it worked.
Thanks Sunny T
Can you post what your final solution was?
Hi Tim,
It's:
Let vKPI_Budget = 'SUM({$' & '<MonthNum ={">=$' & '(=vFMonth)<=$' & '(=vTMonth)"}, Year={"$' & '(=vYear)"}>}[Budget] )';
I'm trying to do something similar but I just can't get it to work. Here's my variable statement in my load script:
let vExp = '=Sum({<Period={$(vPriorPer)},Year={$(vYr)}>} ExtendedPrice)';
This gets evaluated as a formula, but doesn't evaluate to an actual value. My goal is get the value of the expression into the variable so I can load it to another table inline.
Thanks.
Try this:
SET vExp = '=Sum({<Period={#(vPriorPer)},Year={#(vYr)}>} ExtendedPrice)';
LET vExp2 = Replace($(vExp), '#', '$');
Thanks for the suggestion, but I don't think it's working. I get an error "unexpected token Sum, expected nothing.", on the vExp2 variable and then it evaluates to Null.