Converting different currencies into one

[cmccafferty]

Hi, probably a simple one, but I have a field to calculate total revenue as this:

Sum ([BD Total Revenue]) - Sum ([BD Total Insurance Revenue]) - Sum ([BD Excursions Revenue])

My company has now started doing bookings in Canada, so my revenue columns are all giving the number but not the currency.

Basically I want a formula to convert any Canadian Dollar (CAD) bookings into £GBP at a rate of 1.7.

The currency field is called [BD Selling Currency].

I tried this, but clearly didn't work:

if ([BD SellingCurrency] = "CAD" , ((Sum ([BD Total Revenue]) - Sum ([BD Total Insurance Revenue]) - Sum ([BD Excursions Revenue]))/1.7), Sum ([BD Total Revenue]) - Sum ([BD Total Insurance Revenue]) - Sum ([BD Excursions Revenue]))

[captain89]

Hi,

i don't see errors in that expression.

Maybe you must change "CAD" in 'CAD'... i don't know.

However you may better manage the currency problem in qlik in the script.

try to create another field in the load sentence:

load

if([BD Total Revenue])='CAD', [BD Total Revenue]/1.7, [BD Total Revenue]) as  [BD Total Revenue GBP]

...

it's far better for the model usability (more quick).

See this post of Henric:

How to populate a sparsely populated field

[cmccafferty]

Thanks I'll give that a try..

One thing, should it be

if([BD Total Revenue])='CAD', [BD Total Revenue]/1.7, [BD Total Revenue]) as  [BD Total Revenue GBP]

or

if([BD SellingCurrency])='CAD', [BD Total Revenue]/1.7, [BD Total Revenue]) as  [BD Total Revenue GBP]

?

[captain89]

the second one...  my mistake.