Counting Instances of Customer Number

[BParsons]

I’ve been working on a report that identifies loans that are to be written off and have one last issue before it’s done. The problem is that in situations where a customer is listed multiple times it lists their total deposit balance for each instance (used for calculating net charge off amount) which causes the data to be slightly inaccurate. What I was thinking is that if I could identify the first instance of each customer on the report and only include the deposit balance for that instance it should work, but I’m not sure how or if this can be done.

I’m open to any suggestions if anyone has another way to do this but whatever the solution is it has to be able to be used in a formula and be summarized.

CR 10
DB2 Database

Thanks,

Brad

 

[lupins46]

How do you define 'first'? Given a random list of records for a customer how would you know which was the first?

 

[BParsons]

Just the first time the customer appears on the report. It's grouped by loan types so a customer could be included in the auto loan group and the credit card group, if the auto group comes first the balance would be with the auto loan and not the credit card. But, a customer could also have two loans in the same group so in that case it would need to be included for only one of those instances.

Thanks

 

[lbass]

If you have the option of using a SQL expression, create one like {%mindate}:

(select min(AKA.`Date`) from Table AKA where
AKA.`Customer` = Table.`Customer`)

You would substitute your exact field names for "Date" and "Customer" and your table name for "Table". Leave "AKA" as is, since it is an alias table name.

Then go to the record selection formula and enter:

{table.date} = {%mindate}

This should pull only the earliest record for each customer.

-LB

 

[BParsons]

I still want to see all of the records but only attach the deposit balance to one of them.

Thanks

 

[lbass]

You could then remove the SQL expression from the record selection formula and create a formula:

if {table.date} = {%mindate} then {table.depbal} else 0

You could then insert summaries on this.

-LB