Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Set Analysis Variable

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 ?

11 Replies
Not applicable

Set Analysis Variable

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

Not applicable

Set Analysis Variable

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
Valued Contributor II

Set Analysis Variable

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
Valued Contributor III

Re: Set Analysis Variable

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.

Not applicable

Set Analysis Variable

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)

Not applicable

Re: Set Analysis Variable

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.

Not applicable

Re: Set Analysis Variable

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

Not applicable

Re: Set Analysis Variable

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.

Not applicable

Re: Set Analysis Variable

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

Community Browser