Dollar-sign expressions

[#1]

I have this:

SET vCurrTZPrefix=TZPrefix;
SET vDateTime = "=$(vCurrTZPrefix) & 'DateTime'";
SET vStartDT = "=$(vCurrTZPrefix) & 'StartDate'";
SET vEndDT = "=$(vCurrTZPrefix) & 'EndDate'";

Everything is fine till this point. But when I try to do this:

SET formula='sum(if($(vDateTime)=$(vStartDT) and $(vStartDT)=$(vEndDT),
EndMin - StartMin,if($(vDateTime)=$(vEndDT), EndMin,
if($(vDateTime)=$(vStartDT), 60 - StartMin, 60))))/60';

I'm getting this in the runtime variable "formula":

sum(if(=TZPrefix & ''DateTime''==TZPrefix & ''StartDate'' and
=TZPrefix & ''StartDate''==TZPrefix & ''EndDate'', EndMin - StartMin
, if(=TZPrefix & ''DateTime''==TZPrefix & ''EndDate'', EndMin
, if(=TZPrefix & ''DateTime''==TZPrefix & ''StartDate'', 60 - StartMin, 60))))
/60

instead of expected:

sum(if($(vDateTime)=$(vStartDT) and $(vStartDT)=$(vEndDT),
EndMin - StartMin,if($(vDateTime)=$(vEndDT), EndMin,
if($(vDateTime)=$(vStartDT), 60 - StartMin, 60))))/60

Definably, I can type the string for the variable by using a menu. But it will be cleaned if you open a dashboard without data. what is not good.

Does anybody have any idea how to deal with it?

[#2]

It is kind of difficult to view your expressions, because they don't break. When you use [ code ], you have to manually break your lines. And since the message view doesn't have a horizontal scroll bar it is hard to read.

That being said you may have to get rid of the dollar signs in the expression. You want them at the end, but QlikView immediately processes the dollar sign expansions when it sees them. You can use the QlikView function Chr(36) to give you a dollar sign.

Try (I did a replace on the dollar signs, so this may not be exact):

SET formula='sum(if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vStartDT) and
' & Chr(36) & '(vStartDT)=' & Chr(36) & '(vEndDT), EndMin - StartMin,
if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vEndDT), EndMin,
if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vStartDT), 60 - StartMin, 60))))
/60';

You may also need a Let instead of a Set, but I don't know if that is necessary in this case.

[boorgura]

Nick,

Try using the variables with single quotes.

Say, if('$(variable1)' = <xyz>, 'expression')

Let me know if it works.

[boorgura]

I might be wrong though. Dint really go through your expression.

I had a problem with the variables, which was soleved by using the single quotes.

So, suggested so.

[#5 · Accepted solution]

NMiller wrote:
That being said you may have to get rid of the dollar signs in the expression. You want them at the end, but QlikView immediately processes the dollar sign expansions when it sees them. You can use the QlikView function Chr(36) to give you a dollar sign.
Try (I did a replace on the dollar signs, so this may not be exact): <blockquote><pre>SET formula='sum(if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vStartDT) and
' & Chr(36) & '(vStartDT)=' & Chr(36) & '(vEndDT), EndMin - StartMin,
if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vEndDT), EndMin,
if(' & Chr(36) & '(vDateTime)=' & Chr(36) & '(vStartDT), 60 - StartMin, 60))))
/60';

You may also need a Let instead of a Set, but I don't know if that is necessary in this case.

NMiller, Thanks. It works with one BUT the variable should be LET:

Let formula= 'sum(if(' & chr(36) & '(vDateTime)=' & chr(36) & '(vStartDT)
and ' & chr(36) & '(vStartDT)=' & chr(36) & '(vEndDT), EndMin - StartMin,
if(' & chr(36) & '(vDateTime)=' & chr(36) & '(vEndDT), EndMin, if('
& chr(36) & '(vDateTime)=' & chr(36) & '(vStartDT), 60 - StartMin, 60))))/60';