Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
dennysetiawan
Partner
Partner

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
Partner
Author

Dear Expert,

Any suggestion about this case?

dennysetiawan
Partner
Partner
Author

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