Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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.