Using first and Last values in a year for Calculation

[gooboo123]

Hello,

I am tracking my equity profits vs Share market index and I want to calculate the percentage rise in a year in both the profits and the share market index. 

 

Usually I enter the realised, unrealised profits in my database at the end of the day (though not daily, fairly regularly) and I also enter the index value in the database. 

I want to use this data to calculate the percentage rise in my realised, unrealised profits and the index over the year in a report format so that I can have it every year. 

 

I am just lost for ideas to make filemaker select the first and last values for the year. 

 

Any help is much appreciated

 

Thanks 

 

Sanjay

[comment]

The context of your question is not quite clear, esp. this part:

 

in a report format so that I can have it every year.

 

Do you mean to include this in an existing report? Or produce a report of these values? If the latter, you could do a find for records in a given year that do have an entry, sort them by date, and compare the first record to the last using the GetNthRecord() function.

[Fitch]

There are a number of approaches you could take. E.g.:

 

1. Create a calc or auto-entry field: Year(entry date)

2. Create a table, let's call it Report, and make a Year field

3. Create a relationship year to year, and sort it by entry date

4. On your Report layout, put the report year field and the related::value

5. Create a new record in Report and enter a year

 

Now, your related value will be the first value for that year. Repeat steps 3 and 4 but sort this relationship by date descending. Now you've got the last value for the year. You can use these two related values for your profit calculations, which you would create in the Report table.

 

With this setup, you create a record in Report for each year as needed.

[gooboo123]

Hello

Thanks for the replies

I must have not asked the question in the right manner. 

 

What I wanted to know is ... 

Eg - I have 20 entries for each year - of the Share market Index, what my unrealised profit is and what my realised profit is for each of those entries.

I want to create a calculation so that the program automatically gives me during the year what was the percentage increase in the share market index, my unrealised profit and realised profit. 

 

Not necessarily in a report format but just so that it automatically sorts the records in year and then date format. For the year it takes into account the first and last entries and then calculates the percentage change in index, and profit for that year. 

 

I have manually calculated it for this year but wanted to know if it is possible to do it for future years. 

 

Thanks for your replies and help

 

Sanjay

[Fitch]

I explained one method for determining the first and last dates for a given year.

 

My example used the context of a separate report table, and a particular view -- but you could, for example, in the context of the current record, use its year and a self-join relationship. The first related record is provided for "free" by the sort order of the relationship (or even an unsorted relationship, provided the records were entered chronologically) and for the last record of the year you could create a calculation using the Last function.

[comment]

Eg - I have 20 entries for each year - of the Share market Index, what my unrealised profit is and what my realised profit is for each of those entries.

 

I guess I misunderstood your first post: I thought you had some records with partial data so that, for example, the first record in a year with the index value was not necessarily the first record of the same year to have a profit value. If that's not the case, then you can simply sort your records by date and summarize them by year.

 

Assuming the sub-summary part by year is leading, then the starting value is taken directly from the first record in the group, and the last value can be retrieved using the GetNthRecord() function. Use =

 

GetSummary ( sCount ; cYear )

 

to calculate the number of records in each group.