Getting the Previous Contract’s Date and Amount

[LoKi_asterix]

Hi all,

I’m trying to figure out how to pull the previous contract’s details from a master table. Here’s the setup:

CustomerID, ContractID, TransDate, Disbursed Amount

Every time a contract is completed, a new ContractID is created. What I need is:

For any current contract, get the date and amount of the previous contract.

If a customer has multiple past contracts, I only want the most recent one before the current contract.

I’d love to see how you’d approach it, either in the script or using chart expressions. Any tips, examples, or best practices would be great.

Thanks!

[QFabian · Accepted solution]

Hi @LoKi_asterix, one possible option is this script, that create a couple of new fields with the desired data using very common  peek and previous inter record functions 

Script:

Data:  ///some test data

Load * INLINE [

CustomerID, ContractID, TransDate, Amount

1, 1, 31-10-2025, 100

2, 1, 31-10-2025, 200

3, 1, 31-10-2025, 100

4, 1, 31-10-2025, 80

5, 1, 31-10-2025, 90

1, 2, 01-11-2025, 100

];

Load    // here using previous value to compare

CustomerID, 

ContractID, 

TransDate, 

Amount,

if(CustomerID = previous(CustomerID) and TransDate > previous(TransDate), 

    previous(TransDate)) as Previous_TransDate,

if(CustomerID = previous(CustomerID) and TransDate > previous(TransDate), 

    Previous(Amount)) as Previous_Amount   

Resident Data

Order By   // order of fields and their data is very important to make inter record treatments

CustomerID, 

TransDate;

drop table Data;

exit script;

And this is the resulting table :

[Vegar]

@LoKi_asterix 

The suggested solution by @QFabian is pretty much in line with how I would solve this as well.

I suggest you give it a try, and accepy the suggestion as a solution if it turned out to fit your needs, or add a new comment here if it is not what you are looking for.