Calculating Across Records

[abp]

I need an elegant solution for appending sequential serial numbers to parent file ids in a child file. Scenario: I am trying to calculate investment performance for multiple investment portfolios. I have two files: a portfolios.fp5 (parent) and a performance.fp5 (child). The performance file keeps monthly performance data and has cumultive performance calculations, requiring me to use a self-join so that I can use data from previous records (ie., 1 month back, 6 months back, 120 months back, etc.). This is no problem really. The problem arises when I attempt try to track more than one portfolio in the file.

I can track the performance of one portfolio in one file and calculate cumulative and average annual performance by geometrically linking previous records' monthly performance. I go back 120+ records; however, sequential serial numbers are critical to the problem. Alternatively, since the performance records all have month end dates (report_date), I could also link records by using Date(Month(report_date), 1, Year(report_date) ) -1.

Either way, the problem occurs when I reference another portfolio in the performance file, then my sequential numbering gets screwed up.

I suppose I'd like my performance file to serializes itself using the portfolio_id from a separate portfolio file. For instance:

Portfolio_ID & NumToText(Serial)

But it must be in sequential order so that the performance calculations work. Again, a solution with a date might help too. Please, someone, help. Thanks.

[kennedy]

Not sure I followed all that, but my quick guess is this:

When you move from one portfolio to many, your self-join on that file needs to include the portfolio ID as part of the match in the self-join... you only want to join with the relevant (same portfolio) records.

Well, maybe that tidbit will be helpful.