Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
vilstrup
Contributor III
Contributor III

Sum spend in the right currency

Hi,

I have a problem with defining the right expression in my tables.

I am currently working on an application that shows our spend on different sites.

My problem is that i want to show the spend in the right currency. If fx the application is used in the US, everything will be shown in USD.

Im extracting my data from a table with this information:

table.PNG

In this example, the cost is shown in DKK even though the buy is in EUR.

The problem with my current expression (in red) is this:

=SUM(If(DATAAREAID_Trans='sdx',COSTAMOUNTPOSTED,COSTAMOUNTPOSTED*(EXCHRATE/100)))


DATAAREAID tells me from which site the transactions is coming from. In this example 'sdx' is located in the US, so I want all the numbers to be converted to USD.

COSTAMOUNTPOSTED(Cost in tabel) is the amount of spend in the currency related to the DATAAREAID.

So even though the buy is made in EUR the cost is shown in DKK if the site is located in Denmark and so on.

I have a currencytabel but because the currency is originally made in EUR the spend is calculated from EUR to USD instead of DKK to USD.

I need something that says that:

if DATAAREAID_Trans = 'sdx' then COSTAMOUNTPOSTED

else if DATAAREAID_Trans = 'rme' then COSTAMOUNTPOSTED*(EXCHRATE/100) where EXCHRATE = 'DKK'



I just have no idea on how to get this done so every help would be much appreciated!



Kind regards Nicolai

18 Replies
pradosh_thakur
Master II
Master II

May be this


pick(match(DATAAREAID_Trans,'sdx',rme'),COSTAMOUNTPOSTED,COSTAMOUNTPOSTED*(DKK/100)


tell me What exactly EXCHRATE is and i shall tell you the sum expression .


regards

Pradosh

Learning never stops.
vilstrup
Contributor III
Contributor III
Author

Hi Pradosh,

EXCHRATE is the Exchange table i have made.

It tells me what the selected currency where at the present day.

in EXCHARATE there is CURRENCYCODE which could be USD, EUR, DKK, PLN and so on.

I need the EXCHRATE do pick DKK when DATAAREAID = 'rme'

And USD when DATAAREAID = 'sdx'

and EUR when DATAAREAID = 'inn'

Does this make sense?

best regards Nicolai

pradosh_thakur
Master II
Master II

The  statement worked for you? may be with or without sum



sum(pick(match(DATAAREAID_Trans,'sdx',rme'),COSTAMOUNTPOSTED,COSTAMOUNTPOSTED*(DKK/100)))

Learning never stops.
vilstrup
Contributor III
Contributor III
Author

It doesn't work no. I need to define that the CURRENCYCODE = 'DKK' when DATAAREAID = 'rme'

pradosh_thakur
Master II
Master II

Why do you need FROMDATE in you EXCHRATE table you only need CURRENCY CODE and exchrate column . qlikview will do everything automatically

somwthing like

load * inline

[curr_code,exchrate

usd,1

eur,2

sample,3

]

then use the expression

sum(COSTAMOUNTPOSTED*exchrate_fieldname)

regards

Pradosh

Learning never stops.
vilstrup
Contributor III
Contributor III
Author

I need FROMDATE to match the right currency to the date of the buy.

So IF DATAAREAID = 'sdx' then CURRENCYCODE = 'USD' and it will then take the right EXCHRATE from the specific date.

balar025
Creator III
Creator III

Please find attachment.

balar025
Creator III
Creator III

Can you please share demo file?

pradosh_thakur
Master II
Master II

u already have FROMDATE and CURRCODE IN your main table so why not try with what i suggested and see if it works.

regards

Pradosh

Learning never stops.