topic Re: Use Variable Value(s) in an Expression in QlikView

Use Variable Value(s) in an Expression

Wed, 29 Aug 2012 16:43:59 GMT

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.

Re: Use Variable Value(s) in an Expression

Jason_Michaelides — Wed, 29 Aug 2012 17:20:03 GMT

Try some set analysis:

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

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

Hope this helps,

Jason

Re: Use Variable Value(s) in an Expression

Jason_Michaelides — Wed, 29 Aug 2012 17:22:01 GMT

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

Re: Use Variable Value(s) in an Expression

Wed, 29 Aug 2012 18:30:15 GMT

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!

Re: Use Variable Value(s) in an Expression

Wed, 29 Aug 2012 18:36:24 GMT

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

TAKE THIS

Re: Use Variable Value(s) in an Expression

Wed, 29 Aug 2012 18:45:38 GMT

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

Re: Use Variable Value(s) in an Expression

Jason_Michaelides — Wed, 29 Aug 2012 19:50:10 GMT

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

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

Re: Use Variable Value(s) in an Expression

Thu, 30 Aug 2012 21:23:42 GMT

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.

Re: Use Variable Value(s) in an Expression

Jason_Michaelides — Fri, 31 Aug 2012 05:26:37 GMT

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

Re: Use Variable Value(s) in an Expression

Fri, 31 Aug 2012 21:03:39 GMT

Thanks very much.