topic Re: set variable in script with set analysis in QlikView

set variable in script with set analysis

Suus — Wed, 29 Jul 2015 12:30:04 GMT

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!

Re: set variable in script with set analysis

sunny_talwar — Wed, 29 Jul 2015 12:34:58 GMT

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

Anonymous — Wed, 29 Jul 2015 12:38:57 GMT

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

sasiparupudi1 — Wed, 29 Jul 2015 13:05:47 GMT

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

Suus — Tue, 22 Dec 2015 14:34:48 GMT

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