Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Creating a variable for expression (not working)

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

6 Replies
maxgro
MVP
MVP

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';

Not applicable

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.

zagzebski
Creator
Creator
Author

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

Anonymous
Not applicable

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'

zagzebski
Creator
Creator
Author

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

Anonymous
Not applicable

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.