gain or loss between sucessive reccords

[john9210]

I use the following calculation to determine the investment gain or loss between two successive records. The records are sorted by date descending.

Case

( Get(RecordNumber )>1;Total investment-GetNthRecord ( Total investment;Get(RecordNumber) -1 )

)

The calculation works well except that on the last record in the found set it results in a question mark. How can I eliminate the question mark? I would like the result to be be empty.

[comment]

What type of field is Total investment? And how many records do you have in the found set?

[john9210]

It's a number field. I'm in the process of developing the solution so there are only 14 records. I expect in use, it will grow only to a couple of hundred.

[comment]

I cannot reproduce your issue.

[john9210]

Sorry about that. I changed the calculation after I posted it. ! It is:

Case
( Get(RecordNumber ) ≥ 1;Total investment-GetNthRecord ( Total investment;Get(RecordNumber) +1 )
)

It determines the difference between the current and next records.

The file is attached.

Assets.fmp12

[comment]

Well, if you're on the last record, then this part:

GetNthRecord ( Total investment ; Get(RecordNumber) +1 )

will error out.

You should change your test from:

Get (RecordNumber) ≥ 1

to:

Get (RecordNumber ) < Get (FoundCount)

That will make the last record empty and return a value for the first record (which does have a next record).

[john9210]

That did the trick. Thanks for your help.