Why does this dollar expansion evaluate to blank?

[mmarchese]

If I use this expression in a measure directly, it works fine:

Sum({<[Req. Date]={"<$(=Today())"}>} m_piecesRequired)

But if I put that expression in a variable in the load script, like this:

Set vm_piecesRequiredPastDue = Sum({<[Req. Date]={"<$(=Today())"}>} m_piecesRequired);

...then when I look at the variable in the app, the variable definition will have become this:

Sum({<[Req. Date]={"<"}>} m_piecesRequired)

I've tried many variations of that Set statement, but the "today" part always ends up blank.  What is going on here and what can I do differently?

[rwunderlich]

The script attempts to interpret the value in the $() (DSE), but script does not evaluate expressions in DSE, hence the null.  in order to properly retain the entire expression you need to break up the "$(" into two strings and reassemble like this:

LET vm_piecesRequiredPastDue = 'Sum({<[Req. Date]={"<$' & '(=Today())"}>} m_piecesRequired)';

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Vegar]

Is it important to get the dollar expansion into the variable? Or are you moving it to the script in order to lock the today() value? If so then you could skip the dollarexpansion and replace it with the value of today.

LET vm_piecesRequiredPastDue = 'Sum({<[Req. Date]={"<' & Today() & '"}>} m_piecesRequired)';

[Kushal_Chawda · Accepted solution]

@mmarchese  As variable created with SET doesn't evaluate expression, one more thing you can do is create a separate variable for today and then call it in SET variable expression like below

let vToday = Today();

set vm_piecesRequiredPastDue = Sum({<[Req. Date]={"<$(vToday)"}>} m_piecesRequired)

[mmarchese]

Rob,

Thanks for this.

1) Can you elaborate on how the script does DSE but does not "evaluate expressions"?  Does that mean every time it sees "$(=...)" it will replace "..." with nothing?  (That seems like a really poor design decision on Qlik's part, unless I'm misunderstanding.  Why not either do the real evaluation or leave the text alone?  Who could ever benefit from the text just being deleted?)

2) It seems that this technique won't solve my issue completely as is because this variable is just an intermediate one, so the "today" expression just gets deleted in a later step.  Is there a way to get the expression to survive multiple levels of DSE?  Here's what I mean:

Let vm_piecesRequiredPastDue = 'Sum({<[Req. Date]={"<$' & '(=Today())"}>} m_piecesRequired)';

Set vm_piecesShortfallPastDue = ($(vm_piecesRequiredPastDue) - $(vm_piecesStock));

// Result: 
vm_piecesRequiredPastDue: Sum({<[Req. Date]={"<$(=Today())"}>} m_piecesRequired);  // good
vm_piecesShortfallPastDue: (Sum({<[Req. Date]={"<"}>} m_piecesRequired) - Sum(m_piecesStock)); // today is gone

I know I could sidestep this issue by putting the entire definition in each variable instead of composing variables from other variables, but I'd rather not since it'd be terrible coding practice.  It wouldn't be that bad in this particular case, but some of these formulas end up being half a page of text by the final level.

[mmarchese]

Thanks.  To answer your question, I am just defining it in the script because I like having all the code in one place instead of scattered among the GUI.  But since this particular app reloads frequently, I could get away with setting today's date at load time as you did instead of "live" on the front end.  I will likely go with this idea (or Kushal's equivalent) since it works well with multiple levels of replacement, allowing me to compose one variable from others without a headache.

[mmarchese]

Thanks, I like this.  I had tried this method myself but mistakenly used "set" instead of "let" when defining vToday, so it didn't work.  Now I see it's actually viable.