Referencing a value in a colum on another row using FieldValue / FieldIndex

[#1]

Hi All

I'm trying to use FieldValue with FieldIndex (in a straight table chart expression) to generate a new value field, populated with a value in a field on a different row - to generate a value after investment based on when the investment takes place.  I think in Excel I'd use Offset and  / or Index ...

The value then needs to restart based on when the investment is made.

I have the following fields:

'id' - 90+ unique values (user supplied value in a listbox captured in a variable vAssetID) - The 'id' is unique but repeats 25 times as its temporal data, with an investment value for each year beginning in 2015.

'year'  - 1-2-1 link  to the 'value_before_investment' (same row combination of Id and year)

'investment_year'  - user supplied year of investment (an input box with variable attached vYearOfInvest)

'nodeid_year' - The unique element - the combination of id & year e.g. id E12T5 and year 2019 = E12T52019

'value_before_Investment' - a list of values increasing with time

'value_after_Investment' - a usually but not always lower value after the investment is made - resets at the point of investment and begins increasing again over time

This is the kind of expression I'm trying to use (clearly not complete and doesn't restart the values in the new sequence in the row below):

=if(nodeid_year =  vAssetID & vYearOfInvest, FieldValue('value_after_investment',FieldIndex('nodeid_year',vAssetID&vYearOfInvest)),value_before_investment)

Problems / yet to be resolved:

I may be using these functions incorrectly as I can't get it to match the strings to match consistently in FieldValue / FieldIndex when concatenating values together (but they do match when comparing strings like for like in an expression),

Once the lookup values do match, and correct 'value_after_investment' is returned at the correct cell / row location, I then need to start the sequence going again from that point.  See graphic below as an example

Any help gratefully received.  Please fire away with questions if any of this is unclear.

Thanks,

Ross

[#2]

Hi All

I can return the FieldIndex as an integer in all rows successfully using this:

=FieldIndex ('nodeid_year',vAssetID&vYearOfInvest)

But when I extend to this:

=FieldValue('value_after_Investment',FieldIndex('nodeid_year',vAssetID&vYearOfInvest))

It returns '-'

Any help gratefully received

Thanks,

Ross

[marcus_sommer]

I don't think that fieldvalue('field', fieldindex(number)) is the right approach for your task then it returned only distinct values in load-order and is completely independent from structures within a table-chart.

I think you will need inter-record functions like above(), below(), top(), bottom(), ... and/or some kind of lookup with pick(match()).

- Marcus