topic Re: $ expansion not working on a Variable in the Load script in QlikView

$ expansion not working on a Variable in the Load script

Mon, 15 Sep 2014 13:04:05 GMT

Good Morning!

I have a bunch of variables that I have moved from the variable overview screen (where I initially created them) to the Script. I was thinking this would allow for easier maintenance.

The variables however so not work once in the script.

The variable in the Variable overview screen is: sum({<Year = {$(=Only(Year)-1)}>}LineSalesAmount)

In the script it's: set vTemp = sum({<Year = {$(=Only(Year))}>}LineSalesAmount);

The result when in variable overview is correct, gives you last year's sum.

The result when in the script is wrong! for some reason it puts in a blank (like it never saw the $ expansion) in the year dimension (I found this by looking at the variable overview screen).

So when placed in the script QV thinks the variable is: sum({<Year = {}>}LineSalesAmount)

so apparently it's ignoring the $ expansion

Mystery? am I doing something wrong?

Re: $ expansion not working on a Variable in the Load script

MK_QSL — Mon, 15 Sep 2014 13:06:14 GMT

In script, you can't use Set Analysis...

This is not allowed...

{<Year = {$(=Only(Year))}>}

Re: $ expansion not working on a Variable in the Load script

Mon, 15 Sep 2014 13:08:26 GMT

Hi,

Try that, it will make the function to be calculated in each selection (you dont need to use the expansion $ symbol):

set vTemp = = sum({<Year = {$(=Only(Year))}>}LineSalesAmount);

Re: $ expansion not working on a Variable in the Load script

Mon, 15 Sep 2014 17:40:09 GMT

This seemed to work, but I can't tell why the numbers aren't adding up. The formula says it's previous year, but the actual value is this year (or ignoring the set analysis).

Check attached sample

Re: $ expansion not working on a Variable in the Load script

Anonymous — Mon, 15 Sep 2014 18:30:58 GMT

hi,

use

let vTemp = '=sum({<Year = {"' & chr(36) & '(=Only(Year)-1)"}>}Sales)';

in script. Problem here is that qlikview tries to evaluate the code after the $. Therefore replace it with it's ascii code.

Best

Stefan

Re: $ expansion not working on a Variable in the Load script

Mon, 15 Sep 2014 19:47:28 GMT

nope, didn't work

I think i'll just out the variables back into the variable overview screen and call it a day

Re: $ expansion not working on a Variable in the Load script

vgutkovsky — Mon, 15 Sep 2014 20:00:02 GMT

But if you do that, QlikView wins, and we can't allow that Try this:

LET vTemp = 'sum({<Year={$';

LET vTemp = vTemp & '(=Only(Year))}>}LineSalesAmount)';

Vlad

Re: Re: $ expansion not working on a Variable in the Load script

Anonymous — Mon, 15 Sep 2014 20:02:23 GMT

it does?!

Re: $ expansion not working on a Variable in the Load script

maxgro — Mon, 15 Sep 2014 20:24:46 GMT

what about?

let vTemp2 = replace ('sum({<Year = {@(=Only(Year))}>} Sales)', '@', '$');

Re: $ expansion not working on a Variable in the Load script

Mon, 15 Sep 2014 20:38:36 GMT

I must've had a typo. Looks like it works!