Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following sameple LOAD script, where I am loading amounts in 3 currencies. I want to allow the user to select the currency being reported by selecting the ReportCurrency value in a list box (with Always One Selected Value set).
Currency:
LOAD * INLINE [
DisplayCurrency,ReportCurrency
EUR, eur
USD, usd
GBP, gbp
];
Deals:
LOAD * INLINE [
amount_gbp, amount_usd, amount_eur
100,150,110
200,300,220
];
SET Vol = '=SUM(amount_$(ReportCurrency))';
The YTDVol script variable seems to evaluate at script run time and does not recognise $(ReportCurrency). If I create a Document Variable as:
=
SUM(amount_$(=ReportCurrency))
this works OK in chart expressions, but not if I use the Vol variable set in the load script.
I've tried various syntaxes without luck yet. Can anyone help?
Thanks
Dan
quick question why dont you use thre variables:
VOL_EUR, VOL_USD and VOL_GBP?
Main reason is beacuse this is a simplified version of my actual script which has several different types of measure in. They are all suffixed with either usd, eur or gbp and I want to allow the user to choose which currency equivalent to choose to display and any experssions will then dynamically change.
Havign separate variables wouldn't allow this switching between currency equivalents.
Thanks
It's an issue with a specific characters in variable in Script
use this line:
LET Vol = '=SUM(amount_'& Chr(36) & '(=ReportCurrency))'
It work's by me
Rds,
AT
I see that but in the Script you wouldnt be able to switch beetwen the variables?
If you have three variables in the script you could still use them with the currency switch in the application.
Artjoms - top man! This works. Thanks for your help!
It's a pleasure for me to help
Discovered that Artjoms' solution only works if there are no dimensions displayed. It seems to aggregated across the whole data set rather than being dimensionally aware.
Anyone have any ideas?
Seems you are right danmarsh.
I could't find the solution exactly you want, but something I have.
Probably the problem is in Sum function. May be Table and Chart Expression do need this function to be written separately. In this version you need one variable, what will create the name of the field you need:
e.g. vReportCurrency with value ='amount_'&Only(ReportCurrency)
Then in Expression use:
=Sum($(vReportCurrency))
As you can see, in this solution you don't need the problematic variable Vol = '=SUM(amount_'&Chr(36)&'(=ReportCurrency))'
Not as frexible as you want, but still some flexibility exists
Unfortunately I can't attach an example
Rds,
AT