How do I configure running total column for balance based on latest/greater date from repeating ID field

See more:

VB10

Hi All

Am designing a loan lending system. I have managed to create most reports but am stuck at one report (Report to filter only records between two dates)- Filtering code is Ok and working well, the issue is the the last running total for field "Balance". My Crystal report fetches data from a query and the fields are as follows respectively:

Example In the first grouping (FERRY 1A) Balance Column SubTotal where ID 4444444 is repeating on 24-August is picked and 22-August dropped then added to the only ID 666666 giving total of (49970.00+70400.00)=120370.00 but my running total configuration produces wrong subTotal of 122400.00 whereby 52,000.00 of ID 444444 for DOP 22-Aug is used instead of 24- Aug.

My configuration in running total Editor are as shown:

Running total name: subBalance

Field to summarize: QryRegLoanPaymentsZonewise.Balance

Type of summary: Sum

Evaluate: On change of Field- QryRegLoanPaymentsZonewise.NatID

Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})

NOTE: 1)For Repeating Balances of a particular NatID, I just want to pick its latest(greater date) to use for totals. 2) I want correct subtotals for each and every group in the details section.

any help shall be appreciated.

ClientZone	DOP	        NatID          PaidAmount       Balance
GROUP 1: FERRY 1A				
FERRY 1A	22-Aug-2018	444444	        26,000.00	 52,000.00
FERRY 1A	24-Aug-2018	444444	        28,000.00	 49,974.00
FERRY 1A	24-Aug-2018	666666	         8,800.00	 70,400.00
FERRY 1A Totals of this group			62,800.00	122,400.00
GROUP 2: FERRY 2A				
FERRY 2A	22-Aug-2018	123456789	 5,000.00	  6,550.00
FERRY 2A	24-Aug-2018	123456789	 5,000.00	  1,550.00
FERRY 2A, Totals of this group			10,000.00	128,950.00
GROUP 3: MWANANZIA				
MWANANZIA	22-Aug-2018	23538307	 3,000.00	192,000.00
MWANANZIA	23-Aug-2018	23538307	 5,000.00	189,997.00
MWANANZIA	24-Aug-2018	23538307	 3,000.00	191,992.00
MWANANZIA	24-Aug-2018	222222	         1,900.00	  9,100.00
MWANANZIA	24-Aug-2018	222222	         2,000.00	  8,999.00
MWANANZIA Totals of this Group			14,900.00	330,050.00
Grand Total:			                87,700.00       330,050.00

What I have tried:

My configuration in running total Editor are as shown:

Running total name: subBalance

Field to summarize: QryRegLoanPaymentsZonewise.Balance

Type of summary: Sum

Evaluate: On change of Field- QryRegLoanPaymentsZonewise.NatID

Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})