Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner - Contributor III
Partner - Contributor III

Determine beginning balance, mutation, and ending balance from daily to monthly transaction

Dear Expert,

I have a daily transaction data like below:

TRX_DATETRX_TYPEAMOUNTBALANCE_BEFOREBALANCE_AFTER
27-Oct-2020Purchase100005000040000
7-Nov-2020Purchase70004000033000
7-Nov-2020Top Up120003300045000
7-Nov-2020Purchase85004500036500
20-Nov-2020Purchase75003650029000
21-Nov-2020Top Up150002900044000
22-Nov-2020Purchase125004400031500
6-Dec-2020Purchase115003150020000
25-Dec-2020Purchase18500200001500

 

From that, I want to create a table that contains summary per month in which there is a beginning balance, mutation based on trx_type, and ending balance. The formulas are as follows:

 

Beginning Balance = Ending Balance in last month

Top Up = Total Amount with TRX_TYPE = Top Up

Purchase = Total Amount with TRX_TYPE = Purchase

Ending Balance = Beginning Balance + Top Up - Purchase

 

My expectation should be like this:

TRX_MONTHBEGGINING_BALANCETOP UPPURCHASEENDING_BALANCE
Oct-205000001000040000
Nov-2040000270003550031500
Dec-20315000300001500

 

Do you have any solution about this? Thank you for all the advice.

Labels (4)
2 Replies
dennysetiawan
Partner - Contributor III
Partner - Contributor III
Author

Dear Expert,

Any suggestion about this case?

dennysetiawan
Partner - Contributor III
Partner - Contributor III
Author

I'm still looking for a solution about this case.