4 Replies Latest reply: Dec 22, 2015 9:34 AM by Suzan Wijnans

# set variable in script with set analysis

I am trying to put my expression as a variable in my script. With a lot of expressions there is no problem, but this one gives me a headache ;-)

If I use this expression directly in my dashboard all goes well. A nice percentage is calculated.

=sum({<Jaar = {\$(=Max(Jaar)-1)}>} [# Netto Verzuimdagen]) / sum({<Jaar = {\$(=Max(Jaar)-1)}>}  [# Dagen Bezetting Netto]);

If I put exactly the same text as variable in my script it fails when called.

SET vExp_VerzuimVorigJaar = sum({<Jaar = {\$(=Max(Jaar)-1)}>} [# Netto Verzuimdagen]) / sum({<Jaar = {\$(=Max(Jaar)-1)}>}  [# Dagen Bezetting Netto]);

Text Box: =\$(vExp_VerzuimVorigJaar)

My result in text box is:

If I leave the '=\$' in text box to check my variable it gives the following result.

Somehow my Function 'Max(Jaar)-1)' is gone.

• ###### Re: set variable in script with set analysis

Try setting it like this may be:

SET vExp_VerzuimVorigJaar = '=sum({<Jaar = {\$(=Max(Jaar)-1)}>} [# Netto Verzuimdagen]) / sum({<Jaar = {\$(=Max(Jaar)-1)}>}  [# Dagen Bezetting Netto])';

• ###### Re: set variable in script with set analysis

Hi,

Use the keyword LET instead of SET. Please see this post for more: let and set difference

• ###### Re: set variable in script with set analysis

Hi

You may not be able to use set expressions in the script. You could may be try

let vExp_VerzuimVorigJaar = sum({<Jaar = {@(=Max(Jaar)-1)}>} [# Netto Verzuimdagen]) / sum({<Jaar = {@(=Max(Jaar)-1)}>}  [# Dagen Bezetting Netto]);

and in your text box =replace('\$(vExp_VerzuimVorigJaar)','@','\$')

HTH

SaSI

• ###### Re: set variable in script with set analysis

Hi,

In the meantime I found the answer. Maybe someone can use the solution...

QlikView scripting gets confused apostrophe ' and \$. Use chr(39) and chr(36) in stead

The following script lines work as a charm:

LET vExp_VerzuimHuidigJaar  = 'Num(Sum({<' & chr(36) & '(vCurrentYear), ' & chr(36) & '(vYTD)>} [# Netto Verzuimdagen 365])/Sum({<' & chr(36) & '(vCurrentYear), ' & chr(36) & '(vYTD)>} [# Dagen Bezetting Netto 365]),' & chr(39) & '\$(vOpmaakPercentage)' & chr(39) & ')';

(Variables:

LET vPreviousYear = 'Jaar = {' & chr(36) & '(=year(today())-1)}';
LET vYTD = '[# t_m Vandaag]={1}';

SET vOpmaakPercentage = '#.##0,00%'; )