Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cmccafferty
Contributor III
Contributor III

Converting different currencies into one

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

3 Replies
captain89
Creator
Creator

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
Contributor III
Contributor III
Author

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

the second one...  my mistake.