How to Create a Custom Calculation in OAC to Determine Bucket Slippage Values?

Krishna90

I need a calculation to count the number of loans from the table below by counting the customer number. If a customer number has one duplicate, then the number of loans is 2. Additionally, I need to count the customers who have changed their loan status from 'Regular' to 'RISK.' We can use id_unique for counting.

date	id_unique	customer_number	loan_status
12-05-2023	1234512	555555	RISK
12-05-2023	1234513	555555	REGULAR
12-05-2023	4534512	444444	RISK
12-05-2023	8634512	666666	REGULAR
12-05-2023	8634513	666666	RISK
13-06-2023	1234512	555555	RISK
13-06-2023	4534512	444444	REGULAR
13-06-2023	8634512	666666	REGULAR
14-06-2023	8634513	666666	RISK
13-06-2023	8634512	666666	REGULAR

Desired Output In OAC Tabular column:

No. of loans	Customer Count
1	1800000
2	200000
3	50000
4	34545
5	534535
6	36546
7	4554

BalagurunathanBagavathy-Oracle

Hi @Krishna90

Please check the formula below helps:
case when (sum(case when loan_status = 'RISK' or loan_status = 'REGULAR' then 1 else 0 end) by customer_number) = 2 then 'Changed' else 'Unchanged' end

Regards,
Bala.

GayathriAnand-Oracle

Hi

For your first requirement you can try creating a calculation with formula like :

AGGREGATE(COUNT(id_unique) BY customer_number)

Thanks

Gayathri

Krishna90

Above calculation provides the total number of loans for each customer. However, I need to create a calculation or parameter based on the number of loans. This will allow me to map each category to this parameter, such as number of loans vs. count vs branch, number of loans vs. cluster by count etc.

To achieve this, I need a calculation that counts customers with their loan counts such as, customers with 1 loan, customers with 2 loans, and so on. please go through the below requirement:

Branch	No.of Loans	Count
ABC	1	20
ABC	2	15
ABC	3	12
ABC	4	34
ABC	5	12
ABC	6	44
XYZ	1	22
XYZ	2	48
CBD	1	33
CBD	2	66
CBD	8	44

SteveF-Oracle

You can additionally explore the documented functions: AGGREGATE AT, AGGREGATE BY, and if using an Oracle Database, including Autonomous, you can explore the database analytics function, Dynamic Clustering.

Have you tried the Auto Insights feature to see what the system will suggest?

Other comments, welcomed.

BalagurunathanBagavathy-Oracle

Hi Krishna,

It is regarding your 2nd requirement. You may have to modify the formula as needed. My assumption is, the loan status changes from regular to risk only once for a customer. The below formula will help you to identify the customers who changed their loan type. The count of this column will give you the number of customers.

case when (count(distinct loan_status) by customer_number) = 2 then 'Changed' else 'Unchanged' end

Regards,

Bala.

Krishna90

@BalagurunathanBagavathy-Oracle Thank you for the support. Is it possible to calculate at a granular level in OAC, such as counting customers who have changed their loan status from REGULAR to RISK? As, there are other loan statuses such as SM1 and SM2.