topic Set Analysis Variable in QlikView

Set Analysis Variable

Mon, 09 May 2011 13:59:37 GMT

Could someone help me with the following please ?

I have an expression like this:

if(AlternativeSupply='Site1',SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={'Site1'}>} Total Data) )

This works fine but I would like to embed the AlternativeSupply into the Sub Site to save me a long list of 'if' statements something like this:

SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={AlternativeSupply}>} Total Data)

but I don't seem to be able to get the syntax right ?

Set Analysis Variable

Mon, 09 May 2011 14:35:07 GMT

if AlternativeSupply is a value inside the Sub Sites Field, you can try to add the ' ' that you use in all the other selections.

Set Analysis Variable

Mon, 09 May 2011 14:48:34 GMT

I am trying to get the Value 'Site1' into the Sub Sites field setting (there are lots of Possible values for this Site1,Site2 etc the value is stored in the AlternativeSupply field.

Set Analysis Variable

martin59 — Mon, 09 May 2011 14:52:53 GMT

Hi,

I think you have to do this :

[code]SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={"$(=Only(AlternativeSupply))"}>} Total Data)[/code]

Hope that helps you

Martin Favier, Acssi, Lille / Nord / France

Re: Set Analysis Variable

nagaiank — Mon, 09 May 2011 14:57:06 GMT

The syntax for field-selection expression in set analysis expression seems to be FieldName = {FieldValue}

You have tried to use FieldName = {FieldName}

Probably, that is why it is failing.

If you add a field to your file after applying the 'If' conditions and add that field in your set expression, it will work. Probably it is a work-around.

As I did not have your data or qvw file, I checked this using my own data in the attached qvw file.

Hope this example helps you to solve your situation.

Set Analysis Variable

Thu, 12 May 2011 12:08:52 GMT

I think that the issue might be that there is no single value for Alternative supply once I have in inside the Sum/Total Function that is why the 'If' version of the expression works and the combined version doesn't. Is there a way to set a variable before the SUM Function that I can then use within it ? So something like:

Variable1 = AlternativeSupply

SUM({<[Measures-Groups]={'Cost'},[Versions]={'Actual'},[Sub Sites]={Variable1}>} Total Data)

Re: Set Analysis Variable

Thu, 12 May 2011 13:28:34 GMT

Well, you could set a cariable in the script with that value and then use the dollar sign expansion to get its value.

Enviado desde mi Blackberry® 3G de Iusacell.

Re: Set Analysis Variable

Mon, 16 May 2011 15:02:19 GMT

Hello,

I think Martin Favier was near the solution.

I think it's not [Sub Sites]={"$(=Only(AlternativeSupply))"} but [Sub Sites] ={"$(AlternativeSupply)"}.

This will replace the variable AlternativeSupply by its value.

Hope this will solve your problem.

Quent

Re: Set Analysis Variable

Tue, 17 May 2011 10:53:10 GMT

Thanks,

This is definately along the lines of what I'm after but it still doesn't seem to work I have put both those lines in the set analysis along with an example of what I'm trying to achieve.

Re: Set Analysis Variable

Tue, 17 May 2011 11:49:43 GMT

Hello,

I don't know if it's normal, but in the example you gave, the variable AlternativeSupply doesn't exist.

That is the reason why it doesn't work here, if in your real document, this variable exists and has a value that is supposed to work, I don't know how to solve your problem, sorry.

edit : I tried to create the variable and put the value "Site1" in it, and it seems to work, the 4th colum (my solution) took the value "14"

Quent

Re: Set Analysis Variable

Tue, 17 May 2011 12:13:47 GMT

Sorry it should have been AlternativeSite but this isn't the issue.I am able to put a single value in a variable as you have but it falls down when I introduce a variable value in the variable.

Thanks for your help anyway I can work around it for now it just limits what I can do in the future with the data.

If it was excel I would use the INDIRECT function that you can use to build function text with variables but I can't find a similar function in Qlikview. Maybe it's not possible.

Re: Set Analysis Variable

johnw — Tue, 17 May 2011 23:59:48 GMT

You can't do it with set analysis because a set is analyzed for the entire chart, not once per row. Well, technically you CAN do it with set analysis, but it's rather complicated, and not always faster than an if(), so let's set that aside for now.

How about just changing the data model to add an AlternativeCost?

LEFT JOIN (YourTable)
LOAD
Site as AlternativeSite
,Cost as AlternativeCost
RESIDENT (YourTable)
;

Of course if you have 50 different fields you want to see for the alternative site, this isn't very practical, but it seems doable for just a field or two. Denormalization isn't a problem since this is all read only, and QlikView's compression should take care of any extra space requirements.