Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
swijnans
New Contributor III

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.

Please help? Thanks!

1 Solution

Accepted Solutions
swijnans
New Contributor III

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

4 Replies

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

thabang_petje
Contributor III

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

sasiparupudi1
Honored Contributor III

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

swijnans
New Contributor III

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

Community Browser