topic Re: Reusable Expression Problem in QlikView

Reusable Expression Problem

robinrouleur — Wed, 25 Nov 2015 05:58:01 GMT

I've managed to create the following reusable expression in the Document's Variable Overview editor but I can't work out how to define it using a LET/SET statement in the load script. The problem is the requirement for single quotes arount the $1 argument. I've tried every alternative for avoiding literal quotes that I can think of but nothing works. Can anyone help?

Chr(40)&Round(100*Sum($(='$1'&vShowDays))/Sum(ServiceRequests),1)&Chr(37)&Chr(41)

The expression takes an argument, eg "Reprocessed" and adds the postfix in the variable "vShowDays" ("14" or "60") to get the actual dimension name, eg "Reprocessed14". It then sums it, converts to % and wraps the result in "(result%). It works perfectly when created in the Variable Overview editor, I just can't find a way to define it in the load script?

Robin

Re: Reusable Expression Problem

Mark_Little — Wed, 25 Nov 2015 09:32:28 GMT

Hi,

Have you tried.

LET ETest = 'Chr(40)&Round(100*Sum($(='$1'&vShowDays))/Sum(ServiceRequests),1)&Chr(37)&Chr(41)';

then $(ETest) when using it.

Mark

Re: Reusable Expression Problem

Gysbert_Wassenaar — Wed, 25 Nov 2015 09:39:39 GMT

Try this:

SET vMyVar = Chr(40)&Round(100*Sum(@(='@1'&vShowDays))/Sum(ServiceRequests),1)&Chr(37)&Chr(41);

LET vMyVar = Replace('$(vMyVar)','@','$');

Re: Reusable Expression Problem

robinrouleur — Thu, 26 Nov 2015 02:36:40 GMT

Thx Mark,

That resuilts in: Chr(40)&Round(100*Sum()/Sum(ServiceRequests),1)&Chr(37)&Chr(41) ie null inside first Sum().

I think this is because the single qiuotes are interpreted as string delimiters so we have two strings with a naked $1 in between. The $1 and the enclosing $ expansion are both evaluated during the LET assignment and since $1 is not defined the $ expansion results in null. The key problem seems to be that there is no way to prevent $ expansions from being evaluated during a SET/LET.

Re: Reusable Expression Problem

robinrouleur — Thu, 26 Nov 2015 03:25:22 GMT

Yep, that works Gysbert, Thank You!