Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Minimum exchange rate that differs across time periods

Hello Qlik Community,

I am creating a sales reporting application, that will show sales across multiple months, but we have some sales that are reported in CAD and the majority are in USD. The problem is that some are reported with a CAD currency but there is no exchange rate listed in the data, so I have to find what the exchange rate is and convert it. I want everything to display in USD, so i need to create some sort of variable or way to find the exchange rate each month and convert the sales. My exchange rate field is either 1 for USD or whatever the canadian exchange rate is for that month so my thoughts were using some sort of Min with an aggregate function:

To visualize my problem, here is the table showing the sales before conversion (Total POS Sales) and the sales after conversion (POSConverted).

FebExchangeRt.PNG

Here is the formula for POSConverted: ExchangeFormula.PNG

and the formula for vMinExchangeRate:

MinFormula.PNG

This works fine when only 1 month is selected... but if I select multiple month's, it uses the lowest exchange rate across the months, whereas I want to use the lowest exchange rate of each separate month:

Multiplemonths.PNG

I think an aggregate function is needed here, something along the lines of Aggr(Min(Exchane Rate), Date) but when I try that, I only return one result for each month, and am having a hard time getting the result to spread to all of the results in the month.

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Aggr(NODISTINCT Min(Exchane Rate), Date)

or

Min(TOTAL <Date> [Exchange Rate])

View solution in original post

3 Replies
sunny_talwar

Try this:

Aggr(NODISTINCT Min(Exchane Rate), Date)

or

Min(TOTAL <Date> [Exchange Rate])

Anonymous
Not applicable
Author

You would probably be better off creating a new column in your script that is converted to US Funds.

Then you would have the original (transnational) currency amount and the normalized (converted to US) amount.

It'll make your expressions throughout your application much easier to manage.

Not applicable
Author

Thanks! That worked fine, gave me the lowest exchange rate for each month