Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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]))
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:
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]
?
the second one... my mistake.