Using set analysis with variables

[#1]

Hi,

I have a table Fact: Concept, Month, ValueEUR

And another table AuxTime: AuxMonth

I use the set analysis expression below:

Sum($<[Concept] = {'RESULTADO'} , [Month] = {'$(=MaxString([AuxMonth]))'}>ValueEUR)

If I use this expression directly in a Chart, the expression works right. However, if I create a variable and then I use the variable in the Chart, it doesn't work. The expression becomes

Sum($<[Concept] = {'RESULTADO'} , [Month] = {''}>ValueEUR)

What I do wrong?

[#2]

More information:

If I create the variable in the script as:

set vValue = Sum($<[Concept] = {'RESULTADO'} , [Month] = {'$(=MaxString([AuxMonth]))'}>ValueEUR)

it doesn't work.

However, if I go to the "pannel variable" and I create there the variable, the expresion goes right.

[johnw]

It isn't working because QlikView is doing the dollar sign expansion when you set the variable rather than waiting until it is used. I remember that fixing it is something trivial, like putting the whole expression in quotes, but I don't think that was it, and I haven't done it myself so I don't have anything to look up. Hopefully someone that's actually done it will let us know. And at least you have a workaround.

[#4]

Thanks John for your answer. I'll try to find out the way to declare this variable in the script.

[#5]

Hi

I think you need to put an EQUAL sign at the front of the expression in the variable.

Regards,

Nigel.

[#6]

Nigel,

What do you mean?

Something like below?

set vValue = '=Sum($<[Concept] = {'RESULTADO'} , [Month] = {'$(=MaxString([AuxMonth]))'}>ValueEUR)'

[#7]

Hi Kim

Yes, that's exactly what I mean.

Nigel.

[#8]

Hi Nigel,

I have tried it, but it doesn't work.

Look the file attached.

[#9 · Accepted solution]

First, I think you need to use LET instead of SET.

Then, you need to concatenate the string in order to make it come out how you want. This worked for me:

LET vSumaScript2 = '=Sum({$<Concept = {RESULTADO} , Month = {' & '$' & '(=MaxString([AuxMonth]))}>} Value)';

[#10]

Hi Kim

Yes, sorry about that, its not quite as simple as I first thought.

The thing is that when you set a variable that uses dollar expansion, QlikView immediately expands the dollar expansion part and sets the variable according to the value. If the value doesn't exist then it simply creates an expression that states something like Month = {""} in the middle of your set analysis expression.

I have come across the problem before and I worked around it by creating a variable that held (for example) your definition of =MaxString([AuxMonth]), then, in the load script you would set you variable to be something like Month = {"$(MaxMonth)"} where MaxMonth holds the above value.

I've been playing around with this for a while, but haven't got the final answer yet (but I need to do some work now).

I would attach something, but I can't firgure out how to attach anything to this message!!!

Nigel.