Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
Thanks this is exactly what i was looking for.
Hi,
your formula works fine but it is marked as error formula. Have a look at attached screenshot. Is it a bug in QV?
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))