Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to Create a Custom Calculation in OAC to Determine Bucket Slippage Values?
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 |
Best Answer
-
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' endRegards,
Bala.0
Answers
-
Hi
For your first requirement you can try creating a calculation with formula like :
AGGREGATE(COUNT(id_unique) BY customer_number)
Thanks
Gayathri
0 -
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
0 -
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.
0 -
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.
0 -
@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.
0
