topic Re:Using set analysis with variables in QlikView

Using set analysis with variables

Fri, 29 May 2009 17:09:19 GMT

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?

Using set analysis with variables

Fri, 29 May 2009 18:20:21 GMT

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.

Re:Using set analysis with variables

johnw — Fri, 29 May 2009 23:06:27 GMT

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.

Re:Using set analysis with variables

Mon, 01 Jun 2009 15:31:21 GMT

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

Re:Using set analysis with variables

Mon, 01 Jun 2009 17:50:46 GMT

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

Regards,

Nigel.

Re:Using set analysis with variables

Mon, 01 Jun 2009 19:48:09 GMT

Nigel,

What do you mean?

Something like below?

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

Re:Using set analysis with variables

Mon, 01 Jun 2009 19:50:02 GMT

Hi Kim

Yes, that's exactly what I mean.

Nigel.

Re:Using set analysis with variables

Mon, 01 Jun 2009 20:04:47 GMT

Hi Nigel,

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

Look the file attached.

Using set analysis with variables

Mon, 01 Jun 2009 21:11:30 GMT

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

Re:Using set analysis with variables

Mon, 01 Jun 2009 21:20:18 GMT

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.

Re:Using set analysis with variables

Mon, 01 Jun 2009 21:22:27 GMT

So, ignore all of my ramblings, although my solution worked for me before the answer above from NMiller is a MUCH better solution, I've just tried it and it works perfectly (and I'll now use that in my own apps).

Cheers all,

Re:Using set analysis with variables

Mon, 01 Jun 2009 21:23:05 GMT

I'm not sure that an intermediate variable will work. I tried it, but QlikView seemed to expand the variable and then expand the value of that variable and I ended up with the same blank spot in the formula.

To attach a document, click the Options tab at the top of the post page. Choose Add/Update Attachment and choose your file.

Re:Using set analysis with variables

Mon, 01 Jun 2009 22:05:49 GMT

Thanks all,

Till now, the best solution is the NMiller one's.