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: 
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
Anil_Babu_Samineni

Try this?

If(DATAAREAID_Trans = 'sdx', Sum(COSTAMOUNTPOSTED),

If(DATAAREAID_Trans = 'rme', Sum({<EXCHRATE = {"DKK"}>} COSTAMOUNTPOSTED) * (Sum({<EXCHRATE = {"DKK"}>} EXCHRATE) / 100))

OR

If(DATAAREAID_Trans = 'sdx', Sum(COSTAMOUNTPOSTED), Sum({<DATAAREAID_Trans = {"rme"}>} COSTAMOUNTPOSTED) * (Sum({<EXCHRATE = {"DKK"}, DATAAREAID_Trans = {'rme'}>} EXCHRATE) / 100))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vilstrup
Contributor III
Contributor III
Author

Hi Anil,

thank you for your fast reply. it seemed like a good idea but it doesn't work unfortunately. it just sums to zero

Anil_Babu_Samineni

When you receive 0 that means, I believe your data model is wrong.

Try these in your text boxes?

1) If(DATAAREAID_Trans = 'sdx', Sum(COSTAMOUNTPOSTED))

2) Sum({<DATAAREAID_Trans = {"rme"}>} COSTAMOUNTPOSTED) * (Sum({<EXCHRATE = {"DKK"}, DATAAREAID_Trans = {'rme'}>} EXCHRATE) / 100)


Then show me the images, Better if you provide sample

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
balar025
Creator III
Creator III

Hello,

You can go with exchange rate table and can convert into single currency. Then go with selection of particular currency which you want to see.

Like

FromCurrency, ToCurrency,rate

USD,USD,1

AUD,USD,0.56

GBP,USD,1.25

Based on selections or join you can divide with value to get value in respective currency or vice versa.

Regards,

ravi

vilstrup
Contributor III
Contributor III
Author

Its the same unfortunately.

textbox.PNG

I think its beacuse the exchangerate table is linked to the CURRENCYCODE ?

currencycode.PNG

balar025
Creator III
Creator III

Can you please try below code?

RateTable:

Load * Inline [

From,To,Rate

USD,USD,1

AUD,USD,0.56

GBP,USD,1.25

];

Fact:

Load * Inline [

Product,Currency,Value

A1,GBP,50

B1,GBP,20

A2,USD,30

B2,AUD,40

];

left join

Load From as Currency

,Rate as CurrencyRate

Resident RateTable;

FactFinal:

Load *,

Value*CurrencyRate as Value_USD

Resident Fact;

Drop table Fact;

balar025
Creator III
Creator III

Hello,

Please find attached app for reference.

Regards,

Ravi

vilstrup
Contributor III
Contributor III
Author

I can't see any attachments?

vilstrup
Contributor III
Contributor III
Author

Okay. I had a look at it with another view since your answers.

What i think i need is some sort of trigger that says that:

if DATAAREAID = 'sdx', CURRENCYCODE = 'USD'

if DATAAREAID = 'rme', CURRENCYCODE = 'DKK'

if DATAAREAID = 'sol', CURRENCYCODE = 'PLN'

and so on...

How would i do that?