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]

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

[#3]

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.

[martin59]

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

[nagaiank]

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.

[#6]

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)

[#7]

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.

[#8]

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

[#9]

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.

[#10]

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