topic Re: Creating a variable for expression (not working) in QlikView

Creating a variable for expression (not working)

zagzebski — Thu, 25 Jun 2015 20:56:14 GMT

I am trying to create variables for all my commonly used expressions. This is just one example below:

Let vFSRevMaxYear = 'Sum({$<[GL Dashboard]={'Revenue'},[Entity Location]={'01-WC,'03-EC'},Calendar_Year={$(=Max(Calendar_Year))}>} [Amount GL])*-1';

This expression doesn't work as I keep getting a "script line error" - is it because of dashes or apostrophes? If so what is the best solution?

Thanks in advance,

Steve

Re: Creating a variable for expression (not working)

maxgro — Thu, 25 Jun 2015 21:10:00 GMT

I think some help here

Escape sequences

try

Let vFSRevMaxYear = 'Sum({$<[GL Dashboard]={''Revenue''},[Entity Location]={''01-WC'',''03-EC''},Calendar_Year={$(=Max(Calendar_Year))}>} [Amount GL])*-1';

Re: Creating a variable for expression (not working)

Thu, 25 Jun 2015 21:27:30 GMT

Hi,

I suggest you to use chr(39) function whenever you to a single quote in a string or chr(34) for double quotes, escaping it will work too, but sometimes it gets messy.

Ryuma Nakano.

Re: Creating a variable for expression (not working)

zagzebski — Thu, 25 Jun 2015 21:31:58 GMT

It didn't give me an error when I did what you recommended, but the variable returns a "0" instead of the correct amount.

Steve

Re: Creating a variable for expression (not working)

Anonymous — Fri, 26 Jun 2015 01:01:12 GMT

Something like that, if I didn't miss anything:

Let vFSRevMaxYear = 'Sum({$<[GL Dashboard]={' &chr(39)& 'Revenue' &chr(39) & '},[Entity Location]={' &chr(39) & '01-WC' &chr(39) & ',' &chr(39) & '03-EC' &chr(39) & '},Calendar_Year={$(=Max(Calendar_Year))}>} [Amount GL])*-1';

Edit: a way to test is to put this into a text box and see if it builds what you need:

= 'Sum({$<[GL Dashboard]={' &chr(39)& 'Revenue' &chr(39) & '},[Entity Location]={' &chr(39) & '01-WC' &chr(39) & ',' &chr(39) & '03-EC' &chr(39) & '},Calendar_Year={$(=Max(Calendar_Year))}>} [Amount GL])*-1'

Re: Creating a variable for expression (not working)

zagzebski — Fri, 26 Jun 2015 02:06:17 GMT

Dang. It runs, but just gives me a "0". If I have the same expression in a text box it gives me a value.

Steve

Re: Creating a variable for expression (not working)

Anonymous — Fri, 26 Jun 2015 02:10:27 GMT

I hope you run it on the front end, not in the script.

Anyway, it's one thing to build this variable but another is to test the expression itself. You have to upload your app for that sort of help. Syntax looks fine to me.