Dynamic field selection using multiple criteria

[#1]

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.

[hic · Accepted solution]

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

[rubenmarin]

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

[awhitfield]

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

[#5]

Thanks this is exactly what i was looking for.

[#6]

Hi,

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

[rubenmarin]

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))