Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone!
I don't know if I search the community enough but PLEASE help or advise on how to get the AVERAGE DAILY BALANCE in a transactional table.
For this I think I need 3 new fields in my table, kindly see below for the sample table with 1 customer record and the expected output in bold and red fonts
cust_no | trxn_date | balance | ADB_Date | ADB | Current_ADB |
10031 | 1-Jan-20 | 0 | 1-Jan-20 | 0 | 890.33 |
10031 | 2-Jan-20 | 100 | 2-Jan-20 | 50 | 890.33 |
10031 | 3-Jan-20 | 345 | 3-Jan-20 | 148.3 | 890.33 |
10031 | 4-Jan-20 | 50 | 4-Jan-20 | 123.75 | 890.33 |
10031 | 5-Jan-20 | 0 | 5-Jan-20 | 99 | 890.33 |
10031 | 6-Jan-20 | 0 | 6-Jan-20 | 82.5 | 890.33 |
10031 | 7-Jan-20 | 560 | 7-Jan-20 | 150.71 | 890.33 |
10031 | 8-Jan-20 | 400 | 8-Jan-20 | 181.88 | 890.33 |
10031 | 9-Jan-20 | 800 | 9-Jan-20 | 250.56 | 890.33 |
10031 | 10-Jan-20 | 1200 | 10-Jan-20 | 345.5 | 890.33 |
10031 | 11-Jan-20 | 1500 | 11-Jan-20 | 450.45 | 890.33 |
10031 | 12-Jan-20 | 1700 | 12-Jan-20 | 554.58 | 890.33 |
10031 | 13-Jan-20 | 0 | 13-Jan-20 | 511.92 | 890.33 |
10031 | 14-Jan-20 | 3200 | 14-Jan-20 | 703.93 | 890.33 |
10031 | 15-Jan-20 | 3500 | 15-Jan-20 | 890.33 | 890.33 |
I plan to do this in the script and guessing I could use peek here? but I am really in the dark on how to maximize that function with this requirement. Really appreciate your help! Thank you!
Hello,
Here you go:
FactData:
LOAD * INLINE [
cust_no, trxn_date, balance, ADB_Date, ADB, Current_ADB
10031, 1-Jan-20, 0, 1-Jan-20, 0, 890.33
10031, 2-Jan-20, 100, 2-Jan-20, 50, 890.33
10031, 3-Jan-20, 345, 3-Jan-20, 148.3, 890.33
10031, 4-Jan-20, 50, 4-Jan-20, 123.75, 890.33
10031, 5-Jan-20, 0, 5-Jan-20, 99, 890.33
10031, 6-Jan-20, 0, 6-Jan-20, 82.5, 890.33
10031, 7-Jan-20, 560, 7-Jan-20, 150.71, 890.33
10031, 8-Jan-20, 400, 8-Jan-20, 181.88, 890.33
10031, 9-Jan-20, 800, 9-Jan-20, 250.56, 890.33
10031, 10-Jan-20, 1200, 10-Jan-20, 345.5, 890.33
10031, 11-Jan-20, 1500, 11-Jan-20, 450.45, 890.33
10031, 12-Jan-20, 1700, 12-Jan-20, 554.58, 890.33
10031, 13-Jan-20, 0, 13-Jan-20, 511.92, 890.33
10031, 14-Jan-20, 3200, 14-Jan-20, 703.93, 890.33
10031, 15-Jan-20, 3500, 15-Jan-20, 890.33, 890.33
];
Results_temp:
NoConcatenate
LOAD
cust_no,
Date(Date#(trxn_date, 'D-MMM-YY'), 'DD-MM-YYYY') as TRXN_Date,
MonthName(Date#(trxn_date, 'D-MMM-YY')) as TRXN_Month,
Sum(balance) as DailyBalance
Resident FactData
GROUP BY
cust_no,
Date(Date#(trxn_date, 'D-MMM-YY'), 'DD-MM-YYYY'),
MonthName(Date#(trxn_date, 'D-MMM-YY'))
;
DROP TABLE FactData;
Results_temp2:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance,
IF((cust_no <> peek(cust_no,-1))
OR
(TRXN_Month<>peek(TRXN_Month,-1)),
1, peek(SubCount,-1)+1) as SubCount,
IF((cust_no <> peek(cust_no,-1))
OR
(TRXN_Month<>peek(TRXN_Month,-1)),
DailyBalance, DailyBalance+ peek(AccumulatedBalance,-1)) as AccumulatedBalance
Resident Results_temp
Order BY
cust_no ASC, TRXN_Date ASC
;
DROP TABLE Results_temp;
Results:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance,
SUM(AccumulatedBalance)/Sum(SubCount) as AverageDailyBalance
Resident Results_temp2
GROUP BY
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance
;
DROP TABLE Results_temp2;
CurrentBalance:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
AverageDailyBalance
Resident Results
;
INNER JOIN(CurrentBalance)
LOAD DISTINCT
cust_no,
Max(TRXN_Date) as TRXN_Date
RESIDENT CurrentBalance
GROUP BY
cust_no
;
LEFT JOIN(Results)
LOAD DISTINCT
cust_no,
AverageDailyBalance as CurrentBalance
Resident CurrentBalance
;
DROP TABLE CurrentBalance;
Code is plug-and-play.
Let me know if you have questions.
Kind regards,
S.T.
Hello,
Here you go:
FactData:
LOAD * INLINE [
cust_no, trxn_date, balance, ADB_Date, ADB, Current_ADB
10031, 1-Jan-20, 0, 1-Jan-20, 0, 890.33
10031, 2-Jan-20, 100, 2-Jan-20, 50, 890.33
10031, 3-Jan-20, 345, 3-Jan-20, 148.3, 890.33
10031, 4-Jan-20, 50, 4-Jan-20, 123.75, 890.33
10031, 5-Jan-20, 0, 5-Jan-20, 99, 890.33
10031, 6-Jan-20, 0, 6-Jan-20, 82.5, 890.33
10031, 7-Jan-20, 560, 7-Jan-20, 150.71, 890.33
10031, 8-Jan-20, 400, 8-Jan-20, 181.88, 890.33
10031, 9-Jan-20, 800, 9-Jan-20, 250.56, 890.33
10031, 10-Jan-20, 1200, 10-Jan-20, 345.5, 890.33
10031, 11-Jan-20, 1500, 11-Jan-20, 450.45, 890.33
10031, 12-Jan-20, 1700, 12-Jan-20, 554.58, 890.33
10031, 13-Jan-20, 0, 13-Jan-20, 511.92, 890.33
10031, 14-Jan-20, 3200, 14-Jan-20, 703.93, 890.33
10031, 15-Jan-20, 3500, 15-Jan-20, 890.33, 890.33
];
Results_temp:
NoConcatenate
LOAD
cust_no,
Date(Date#(trxn_date, 'D-MMM-YY'), 'DD-MM-YYYY') as TRXN_Date,
MonthName(Date#(trxn_date, 'D-MMM-YY')) as TRXN_Month,
Sum(balance) as DailyBalance
Resident FactData
GROUP BY
cust_no,
Date(Date#(trxn_date, 'D-MMM-YY'), 'DD-MM-YYYY'),
MonthName(Date#(trxn_date, 'D-MMM-YY'))
;
DROP TABLE FactData;
Results_temp2:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance,
IF((cust_no <> peek(cust_no,-1))
OR
(TRXN_Month<>peek(TRXN_Month,-1)),
1, peek(SubCount,-1)+1) as SubCount,
IF((cust_no <> peek(cust_no,-1))
OR
(TRXN_Month<>peek(TRXN_Month,-1)),
DailyBalance, DailyBalance+ peek(AccumulatedBalance,-1)) as AccumulatedBalance
Resident Results_temp
Order BY
cust_no ASC, TRXN_Date ASC
;
DROP TABLE Results_temp;
Results:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance,
SUM(AccumulatedBalance)/Sum(SubCount) as AverageDailyBalance
Resident Results_temp2
GROUP BY
cust_no,
TRXN_Date,
TRXN_Month,
DailyBalance
;
DROP TABLE Results_temp2;
CurrentBalance:
NoConcatenate
LOAD
cust_no,
TRXN_Date,
AverageDailyBalance
Resident Results
;
INNER JOIN(CurrentBalance)
LOAD DISTINCT
cust_no,
Max(TRXN_Date) as TRXN_Date
RESIDENT CurrentBalance
GROUP BY
cust_no
;
LEFT JOIN(Results)
LOAD DISTINCT
cust_no,
AverageDailyBalance as CurrentBalance
Resident CurrentBalance
;
DROP TABLE CurrentBalance;
Code is plug-and-play.
Let me know if you have questions.
Kind regards,
S.T.
Hi Stoyan,
This is reallyyyy cool. Thank you SO much! btw, this works great but would you mind explaining to me why it is partitioned monthly? I am hoping for a daily average from the start up to the current date and upon initial testing it resets the averaging in each month, so it's more like Average Monthly Daily Balance? Thank youuu!!!
Hey,
Yeah, indeed it's monthly accumulation. I though you are using this for accumulation for a monthly discount or another similar accounting principle.
If you need an all-time balance, just remove this part everywhere you see it:
OR
(TRXN_Month<>peek(TRXN_Month,-1))
Kind regards,
Stoyan
Hi Stoyan,
Yeah! I figured it out eventually and it works perfectly. Thank you so much!!!!!