topic Converting different currencies into one in QlikView

Converting different currencies into one

cmccafferty — Fri, 07 Sep 2018 15:02:09 GMT

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

Re: Converting different currencies into one

captain89 — Fri, 07 Sep 2018 15:15:49 GMT

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

Re: Converting different currencies into one

cmccafferty — Fri, 07 Sep 2018 15:21:08 GMT

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]

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

Re: Converting different currencies into one

captain89 — Fri, 07 Sep 2018 15:25:09 GMT

the second one... my mistake.