topic Re: Dynamic field selection using multiple criteria in QlikView

Dynamic field selection using multiple criteria

Mon, 16 Mar 2015 14:26:24 GMT

Hello,

I need to give user possibility to select all calculated figures in two different currencies. In my case it will be a local currency and euro. Now i already have columns where local and euro values are calculated e.g. Sales_LOCAL, Returns_Local, Return_Euro and Sales_EURO. I need to give user the possibility to select between euro and local in a selection box and according to the selection select the field. Problem is very easy if there are only two columns but in this case if have more then two column so i need to select a dynamic filed name using multiple criteria.

I created a island table like this

Field Name	Currency
SALES_LOCAL	LOCAL
SALES_EURO	EURO
RETURN_LOCAL	LOCAL
RETURN_EURO	EURO

So user can select local or euro. but i am not able so write the dynamic field name e.g.

if user select LOCAL i need to calculate SUM(SALES_LOCAL) , SUM(RETURN_LOCAL) etc. and if user select EURO then SUM(SALES_EURO) , SUM(RETURN_EURO) etc.

I hope you guys must have a trick to solve it i am hanged in here.

Thank you very much in advance for you help.

Re: Dynamic field selection using multiple criteria

hic — Mon, 16 Mar 2015 14:35:54 GMT

If you create a field "Currency" through

Load * inline

[Currency

EURO

LOCAL];

and define a variable vCurrency through

'=Only(Currency)'

then you can use the following formulas

=Sum(SALES_$(vCurrency))

=Sum(RETURN_$(vCurrency))

See more on The Magic of Variables

HIC

Re: Dynamic field selection using multiple criteria

rubenmarin — Mon, 16 Mar 2015 14:41:43 GMT

Hi Sameer, this can be done in several ways, to tell the two I most use:

first way:

create variables for sales, return... and use it in expressions, ie, for sales:

SET vSales = "=If(CurrencyField='EURO', '[SALES_EURO]', '[SALES_LOCAL]')"

Then you can use expressions like: Sum($(vSales))

This can be done without variables but if you add another currency it will save you a lot of time.

Second way:

Using $-expansion to crete the field name:

Sum([SALES_$(CurrencyField)]) or Sum([SALES_$(=CurrencyField)]) --> It will expand to Sum([SALES_EURO]) or Sum([SALES_LOCAL]) depending on the value selected in CurrencyField

Re: Dynamic field selection using multiple criteria

awhitfield — Mon, 16 Mar 2015 14:45:33 GMT

OR you could created 2 charts, one for local and a second for euro, create a variable that's set from a button, then use a conditional show, see the attached example.

Andy

Re: Dynamic field selection using multiple criteria

Mon, 16 Mar 2015 15:00:03 GMT

Thanks this is exactly what i was looking for.

Re: Dynamic field selection using multiple criteria

Mon, 16 Mar 2015 15:09:55 GMT

Hi,

your formula works fine but it is marked as error formula. Have a look at attached screenshot. Is it a bug in QV?

Re: Dynamic field selection using multiple criteria

rubenmarin — Mon, 16 Mar 2015 15:16:45 GMT

Yes, it happens when you build a field name like this, tha'ts one backward of using this approach, you lost intellisense (or whatever name it has in QV).

For debug in complicated expressions you can replace (CTRL+F) all $(vcurrency) for EURO (be sure there is no other 'EURO' string in the expression), debug expression and revert the replacing when it's debbugged (replacing EURO for $(vcurrency))