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: 
sam_grounds
Contributor III
Contributor III

Variables which reference other variables in the script

Hi all,

I've had a pesky issue in the past with the dollar expansion. A great feature, but can cause problems if you don't know how to avoid it... I often want to write a large number of variables, which I paste into my script, sometimes using a program like excel to use a formulaic pattern to writing them.

An example would be this...

Variable name: vSales

Desired value: num(sum({$<[Transaction Date]='$(vDate)'}>}Sales),'£#,##0')

There a couple of problems with this approach, the first of all is the use of single quotes. If you want to write a single quote into a variable using the script, use the chr(39) function instead.

Simply replace all single quotes with this

'&chr(39)&'

(including the single quotes)

Secondly, the dollar expansion will evaluate the variable there and then and write it in, in place of the variable name, rather than allowing it to reference the variable directly.

In order to stop the dollar expansion evaluating the string contained in the variable, simply break the dollar and opening bracket up. Like so...

$'&'(

Simple, yet effective.

So the best way I have found to write this into the script would be...

LET vSales_Yesterday='num(sum({$<[Transaction Date]={'&chr(39)&'$'&'(vDate)'&chr(39)&'}>}Sales),'&chr(39)&'£#,##0'&chr(39)&')';

This will create a variable...

vSales

which contains...

num(sum({$<[Transaction Date]={'$(vDate)'}>}Sales),'£#,##0')

This approach basically tweaks what is being evaluated the first time (by the script) to produce a string which later on when it is evaluated it can repeatedly use the other variable in the front end expressions.

Hope this helps someone else who encounters the same issue.

Sam

2 Replies
millerhm
Partner - Creator
Partner - Creator

@sam_grounds  - four years later, you've saved me from tearing all of my hair out or banging my head on my desk one too many times - many, many thanks!

jda_bryan
Partner - Contributor II
Partner - Contributor II

@sam_grounds  Same here.   Nice tip.   Thanks!