Set Analysis in an Expression - how to use "value of current row"

[#1]

The project:
In a nutshell: I have a list of invoices which I need to translate to USD with the exchange rate of the day that the user enters into the report. I defined a variable for the date that can be entered - vAging. The exchange rates are imported into a data island and contain  rates for all relevant currencies for the past few years (= too many records to link all rates and filter away those that I do not need in the report).

The problem:

I am looking for a formula which allows me to retrieve the Exchange Rate for the Currency in which each invoice has been issued for the day has been entered as a variable by the user.

Example:
User enters date 25.08.2013 into vAging, the column "Exchange Rate" is the expression that should be populated.

Invoice No	Currency	Amount	Exchange Rate
1234	EUR	100,00	0,7545
1235	GBP	200,00	0,6534

I may also need to get to a total USD value in a more highlevel view, where the individual invoices and their currency codes are not displayed - so either this formula or a separate one should be able to take each invoice within the current selection and translate it at the applicable exchange rate to USD (look up currency from the invoice and exchange rate date from variable).

[#2]

please could u send a simple qvw concerning with ur problem?

[marcus_sommer]

Perhaps it is helpful, in your install folder from fat client under ...\Examples\Documents\ are demo applications and in "Data Visualization.qvw" are similar issues and solutions.

- Marcus

[#4]

Hi Riccardo,

I have not submitted test files so far, so I gave it my best shot. It includes two very simple data tables, an entry field for the variable plus two pivot charts with descriptions on what I tried to achieve in each chart.

Thanks for helping me with this!

Carola

[#5]

Thank you for the suggestion Marcus - I could not find the folder you mentioned  - but I found a similar option in the Community Resources which I will try out to find a solution for my problem.

Kind regards,

Carola

[#6]

try this file, and let me know...

[#7]

Hi Riccardo,

Actually the problem is different: I cannot use the exchange rate of the due date to translate to USD. I need to translate each of these invoices with the exchange rate of the day that the user enters  irrespectively of what the due date is.  In the ExRateNew file, the entered variable acts like a filter and summarizes invoices which are due on that specific date with the exchange rate of that day.

The final report will be an Aged Accouns Receivables report - in my simple ExRates File I omitted the part of the report where I determine which invoice is still open on the entered date and calculate the aging with the CLASS function as this is already working nicely.

I was hoping that my goal could be achieved with a clever set-analysis approach. But I start to feel that I need to have something that allows me to do a calculation over all invoices inside the report on a user-level (as more users can use the same report at the same time with different dates) , store the data in a temp table "somewhere" and then build the pivot charts on this temp table. Or go the long and winding road via an endless IF statement...

Kind regards,

Carola