Input Boxes and Variable expansion

[#1]

Hi,

I'm trying to build an application that shows sales value in the currency of the users choice (EUR, GBP, USD) and using 1 of 3 budget conversion rates (last year/Current/Next). I want to preserve table layout and keep the chart expressions simple so have investigated including the code for the currency requirements as variables.

Successfully added an input box to record the variables SpotCurrency and BudgetYear.

The following code has been added against the variable AmtSpot (which is then used in charts).

code

if

/code

(SpotCurrency='GBP',(AmtHTInv * $(#BudExRateGBP)),(if(SpotCurrency='EUR',(AmtHTInv*$(#BudExRateEUR)),(AmtHTInv*$(#BudExRateUSD)))))

The following code has been added against each included variable e.g. BudExRateGBP

code

if

(BudgetYear='CURRENT',ExRateGBP,if(BudgetYear='NEXT',ExRateGBPNext,ExRateGBPPrev))

/code

The actual rates are held in the fields ExRateGBP, ExRateGBPNext and ExRateGBPPrev on a table which is joined to the sales by virtue of the currency code held on the invoice.

All my charts (using expressions like sum($(AmtSpot)) appear to accept changes to the currency correctly but do not alter when I change the Budget Year. Instead the calculation uses the ExRateGBPPrev rate indicating that the conditional expression for BudExRateGBP is not working. Could it be the listed values? (LAST;CURRENT;NEXT) - documentation says to enclose alphanumeric values in quotes but doing this adds the quote marks into the the variable value??

Incidentally, I expect my variable expressions are likely to be improved by using different conditional functions so any advise in that respect would be appreciated.

many thanks,

Phil.

[#2]

Phil

Any chance you can post your app?

It is difficult to fully understand the model without seeing it.

[#3]

Nigel - sorry for the delay. I've posted my app with a cut down set of data into my files. Can you see this or do I need to post somewhere else on the community pages?

thanks,

Phil.