Minimum exchange rate that differs across time periods

[#1]

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

Here is the formula for POSConverted:

and the formula for vMinExchangeRate:

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:

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.

[sunny_talwar · Accepted solution]

Try this:

Aggr(NODISTINCT Min(Exchane Rate), Date)

or

Min(TOTAL <Date> [Exchange Rate])

[Anonymous]

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.

[#4]

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