Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to calculate how many applications are sanctioned. The criteria is: Sanctioned = Sanctioned-not Booked+Booking Started+Lender Decision for the following data. Can I just use <Case expression> to get the above or would have to must use Appl_ID
APPL_ID | <CASE expression> |
19681349 | ENTERED |
19737483 | DECLINED |
11089833 | Booking Started |
11829485 | BOOKED |
13145469 | DECLINED |
10510490 | DECLINED |
15079449 | BOOKED |
16357070 | BOOKED |
17715414 | Lender Decision |
19097166 | BOOKED |
12671357 | DECLINED |
12973150 | DECLINED |
13417909 | DECLINED |
15294770 | BOOKED |
17432881 | SYSTEM DECISION |
13579447 | BOOKED |
19212301 | Sanctioned-Not Booked |
10369751 | REFERRAL EVENT |
15752122 | BOOKED |
14255114 | DECLINED |
10240186 | DECLINED |
19214097 | Cancelled |
15874564 | BOOKED |
12260756 | DECLINED |
11978952 | BOOKED |
15505655 | Cancelled |
11368921 | BOOKED |
11087854 | Lender Decision |
13137807 | BOOKED |
16159271 | Cancelled |
16510501 | BOOKED |
10393592 | BOOKED |
10007616 | BOOKED |
11916336 | BOOKED |
18191506 | BOOKED |
14763573 | BOOKED |
10256936 | REFERRAL EVENT |
18988955 | BOOKED |
13848021 | DECLINED |
17872078 | BOOKED |
16139673 | BOOKED |
16067788 | Sanctioned-Not Booked |
use below in script..
TEMP:
Load * Inline
[
APPL_ID, CASE
10007616, BOOKED
10240186, DECLINED
10256936, REFERRAL EVENT
10369751, REFERRAL EVENT
10393592, BOOKED
10510490, DECLINED
11087854, Lender Decision
11089833, Booking Started
11368921, BOOKED
11829485, BOOKED
11916336, BOOKED
11978952, BOOKED
12260756, DECLINED
12671357, DECLINED
12973150, DECLINED
13137807, BOOKED
13145469, DECLINED
13417909, DECLINED
13579447, BOOKED
13848021, DECLINED
14255114, DECLINED
14763573, BOOKED
15079449, BOOKED
15294770, BOOKED
15505655, Cancelled
15752122, BOOKED
15874564, BOOKED
16067788, Sanctioned-Not Booked
16139673, BOOKED
16159271, Cancelled
16357070, BOOKED
16510501, BOOKED
17432881, SYSTEM DECISION
17715414, Lender Decision
17872078, BOOKED
18191506, BOOKED
18988955, BOOKED
19097166, BOOKED
19212301, Sanctioned-Not Booked
19214097, Cancelled
19681349, ENTERED
19737483, DECLINED
];
MAIN:
Load *, IF(CASE = 'Sanctioned-Not Booked' or CASE = 'Booking Started' or CASE = 'Lender Decision', 'Sanctioned',CASE) as SCND Resident TEMP;
Drop Table TEMP;
----------------------------------------
1) Select PIE chart
2) Dimension = SCND
3) Expression 1 = COUNT(APPL_ID)
Tick Value on Data Points
4) Expression 2 = if(Count(DISTINCT SNCD)=1, SCND)
Tick Value on Data Points
5) Presentation Tab
Untick Show Legend....
Manish, thanks for that but I have loads of data. Is it possible to get the above with an expression instead of code.
you can use the same as Calculated dimension..
IF(CASE = 'Sanctioned-Not Booked' or CASE = 'Booking Started' or CASE = 'Lender Decision', 'Sanctioned',CASE)
didnot work