topic Re: Average Daily Balance in App Development

Average Daily Balance

Badzreyes00 — Sat, 16 Nov 2024 02:21:59 GMT

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!

Re: Average Daily Balance

Stoyan_Terziev — Thu, 21 May 2020 16:32:11 GMT

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.

Re: Average Daily Balance

Badzreyes00 — Fri, 22 May 2020 06:49:07 GMT

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!!!

Re: Average Daily Balance

Stoyan_Terziev — Tue, 26 May 2020 09:58:26 GMT

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

Re: Average Daily Balance

Badzreyes00 — Tue, 26 May 2020 12:17:41 GMT

Hi Stoyan,

Yeah! I figured it out eventually and it works perfectly. Thank you so much!!!!!