Use Variable Value(s) in an Expression

[#1]

I hope this is an easy question, I have 1 variable, vCity that someone can select from a predefined list of cities that I need to use for comparing to elements in the app and summing up values.  Here is an example of what I need to do:

Example:

An end user selects whatever city they are interested in on the front end.  For example.

vCity could = Miami, NY, London, NULL, all values or 1 or more values, etc.

Then say I have multiple cities data types loaded in the app, such as the cities of the sales person (SalesCity) and the cities of the client (ClientCity), etc.

Depending on the context, I am using an IF statement to sum up values using the appropriate city type.  So in my expressions I just need to set the value of SalesCity:=vCity or ClientCity:=vCity depending on which one I need to use.   Just as if the person had choosen SaleCity or ClientCity directly.

Is there an easy way to do this?

Thanks very much.

[Jason_Michaelides]

Try some set analysis:

Sum({<ClientCity={'$(vCity)'}>} Sales)

or

Sum({<SaleCity={'$(vCity)'}>} Sales)

Hope this helps,

Jason

[Jason_Michaelides]

(PS - make sure vCity is a comma-delimited list if multiple values are selected, or * for all)

[#4]

One follow up, what if the person selects Miami and New York as the value for vCity?   So vCity = Miami,New York.

This does not seem to be working using your sum statement.  Although it works PERFECTLY if vCity = Miami OR New York only, so THANKS very much for that!

[#5 · Accepted solution]

Sum({<ClientCity={'$(#vCity)'}>} Sales) OR Sum({<ClientCity={$(#vCity)}>} Sales)

TAKE THIS

[#6]

Very much appreciated!  The second one did the trick,  Sum({<ClientCity={$(#vCity)}>} Sales).

Either of you know ANYWHERE there is basic (clearly i am basic ) level training material I can get online on set analysis that includes lots of good examples? 

Thanks again.

Richard

[Jason_Michaelides]

Start by reading the F1 help section on Set Analysis, then try this wizard:

http://www.qlikblog.at/1384/set-analysis-wizard-qlikview/

[#8]

Thanks.... of course F1 Help on my app isn't working, but found useful examples in Reference PDF.

One more syntax challenge, as part of this trying to convert an end user entered Date (on calendar element) inside the expression, in a normal If statement it works fine as Date(vDateEntered) to converts successfully from number  to date format. 

But in this example it isn't converting to date mm/dd/yyyy format.

=sum({<SalesDate={Date$(vDateEntered)}>} Sales) 

(just summing up sales for whatever date a person picked)

Can you tell me the right syntax for this one?   Thanks again.

[Jason_Michaelides]

Try:

=sum({<SalesDate={"=Date($(vDateEntered),'mm/dd/yyyy')"}>} Sales) 

Depending on the expression for vDate you may not need the dollar-expansion around it.

Hope this helps,

Jason

[#10]

Thanks very much.