Set Analysis Variable

[#1]

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 ?

[#2]

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.

[johnw]

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.