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: 
Suus
Partner - Contributor III
Partner - 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
Suus
Partner - Contributor III
Partner - Contributor III
Author

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

View solution in original post

4 Replies
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
Not applicable

Hi,

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

sasiparupudi1
Master III
Master III

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
Partner - Contributor III
Partner - Contributor III
Author

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