5 Replies Latest reply: Oct 7, 2016 12:44 PM by Sunny Talwar

# Set Analysis

I've linked a list of closing balances to a set of currency exchanges rates using the date of the balance and the MatchInterval of when the conversion rate was valid. I have multiple currencies in both fields so want to return when both currencies match and apply the correct exchange rate.

``sum( {\$ <BalanceAutoNumber={"=BalanceCurrency=Currency"}>} ClosingBalance*CADMultiply) ``

As per an online guide, I have added the field BalanceAutoNumber to the Balances table as my dimension on which to select on. However, I'm only getting results when there I manually select in both fields.

Data looks something like this

Balances

BalanceAutoNumberDateForCurrencyCurrencyClosingBalance
229/10/2015GBP100
329/10/2015USD200

CurrencyRates

01/10/201531/10/2015GBP1.5
01/10/201531/10/2015USD1.2
01/10/201531/10/2015JPY1000

The tables are linked using interval match between DateForCurrency, StartDate and EndDate

• ###### Re: Set Analysis

Why do you even need set analysis after interval match?

Script:

Balances:

DateForCurrency,

Currency,

ClosingBalance

FROM

(html, codepage is 1252, embedded labels, table is @1);

CurrencyRates:

EndDate,

Currency,

FROM

(html, codepage is 1252, embedded labels, table is @2);

IntervalMatch:

IntervalMatch (DateForCurrency, Currency)

EndDate,

Currency

Resident CurrencyRates;

• ###### Re: Set Analysis

Thanks Sunny, this is what I have done myself to get the answers. However, I wanted to know how to achieve this using Set Analysis so that I can develop those skills.

• ###### Re: Set Analysis

I doubt that this can be done using set analysis, may be we can use Aggr() function to get this done, but set analysis might not be possible unless you are willing to cross join the two tables into one.

• ###### Re: Set Analysis

Thanks Sunny, just to clarify in my head - can Set Analysis only be run across a flat data set?

• ###### Re: Set Analysis

It can run across different date sets connected properly, but when you are trying to equate two values, that's when it starts to get tricky.