set variable in script with set analysis

[Suus]

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.

Please help? Thanks!

[sunny_talwar]

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

[Anonymous]

Hi,

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

[sasiparupudi1]

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

[Suus · Accepted solution]

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