Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Badzreyes00
Contributor III
Contributor III

Average Daily Balance

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_notrxn_datebalanceADB_DateADBCurrent_ADB
100311-Jan-2001-Jan-200890.33
100312-Jan-201002-Jan-2050890.33
100313-Jan-203453-Jan-20148.3890.33
100314-Jan-20504-Jan-20123.75890.33
100315-Jan-2005-Jan-2099890.33
100316-Jan-2006-Jan-2082.5890.33
100317-Jan-205607-Jan-20150.71890.33
100318-Jan-204008-Jan-20181.88890.33
100319-Jan-208009-Jan-20250.56890.33
1003110-Jan-20120010-Jan-20345.5890.33
1003111-Jan-20150011-Jan-20450.45890.33
1003112-Jan-20170012-Jan-20554.58890.33
1003113-Jan-20013-Jan-20511.92890.33
1003114-Jan-20320014-Jan-20703.93890.33
1003115-Jan-20350015-Jan-20890.33890.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!

1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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.

View solution in original post

4 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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.

Badzreyes00
Contributor III
Contributor III
Author

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

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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

Badzreyes00
Contributor III
Contributor III
Author

Hi Stoyan,

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