I have one table (Fund Characteristics) that contains characteristics of mutual funds
Ticker Company Asset class ...
I have another table (Fund Assets) that contains the assets in each fund
Ticker Date Assets
They are linked 1:* on Ticker.
I have a third table (Invested assets) that contain how much an investor has invested in each fund
Client Date Ticker Amt Invested
This file is also linked 1:* with Fund Charasteristics on Ticker. And so far life is happy.
We can classify a fund as Small, Medium, or Large. I want to see if investors have a preference for a particular fund size. Ideally, Fund Size should be in the charasterics table. But Fund size varies by time, so I have to have it Fund Assets table. I added two calculated columns to Funds Assets table: FundSize and TickerDate = concatenate(Ticker,Date).
I then create TickerDate in Invested Assets table and linked to Fund Assets. this relationship isn't active as the tables are still linked using Ticker. I want a table with
Rows: Client, Date (from Invested assets table)
Columns: Fund Size (from Fund Assets table)
Value: Amt Invested (from Invested assets table)
But this gives me an error b/c BI can't determine the relationship between two or more fields.
Many thanks!
