https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1775452#M591062 <P>Dear Expert,</P><P>Any suggestion about this case?</P> Tue, 19 Jan 2021 07:11:20 GMT dennysetiawan 2021-01-19T07:11:20Z https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1775136#M591061 <P>Dear Expert,</P><P>I have a daily transaction data like below:</P><TABLE><TBODY><TR><TD>TRX_DATE</TD><TD>TRX_TYPE</TD><TD>AMOUNT</TD><TD>BALANCE_BEFORE</TD><TD>BALANCE_AFTER</TD></TR><TR><TD>27-Oct-2020</TD><TD>Purchase</TD><TD>10000</TD><TD>50000</TD><TD>40000</TD></TR><TR><TD>7-Nov-2020</TD><TD>Purchase</TD><TD>7000</TD><TD>40000</TD><TD>33000</TD></TR><TR><TD>7-Nov-2020</TD><TD>Top Up</TD><TD>12000</TD><TD>33000</TD><TD>45000</TD></TR><TR><TD>7-Nov-2020</TD><TD>Purchase</TD><TD>8500</TD><TD>45000</TD><TD>36500</TD></TR><TR><TD>20-Nov-2020</TD><TD>Purchase</TD><TD>7500</TD><TD>36500</TD><TD>29000</TD></TR><TR><TD>21-Nov-2020</TD><TD>Top Up</TD><TD>15000</TD><TD>29000</TD><TD>44000</TD></TR><TR><TD>22-Nov-2020</TD><TD>Purchase</TD><TD>12500</TD><TD>44000</TD><TD>31500</TD></TR><TR><TD>6-Dec-2020</TD><TD>Purchase</TD><TD>11500</TD><TD>31500</TD><TD>20000</TD></TR><TR><TD>25-Dec-2020</TD><TD>Purchase</TD><TD>18500</TD><TD>20000</TD><TD>1500</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>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:</P><P>&nbsp;</P><P>Beginning Balance = Ending Balance in last month</P><P>Top Up = Total Amount with TRX_TYPE = Top Up</P><P>Purchase = Total Amount with TRX_TYPE = Purchase</P><P>Ending Balance = Beginning Balance + Top Up - Purchase</P><P>&nbsp;</P><P>My expectation should be like this:</P><TABLE><TBODY><TR><TD>TRX_MONTH</TD><TD>BEGGINING_BALANCE</TD><TD>TOP UP</TD><TD>PURCHASE</TD><TD>ENDING_BALANCE</TD></TR><TR><TD>Oct-20</TD><TD>50000</TD><TD>0</TD><TD>10000</TD><TD>40000</TD></TR><TR><TD>Nov-20</TD><TD>40000</TD><TD>27000</TD><TD>35500</TD><TD>31500</TD></TR><TR><TD>Dec-20</TD><TD>31500</TD><TD>0</TD><TD>30000</TD><TD>1500</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Do you have any solution about this? Thank you for all the advice.</P> Sat, 16 Nov 2024 17:36:24 GMT https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1775136#M591061 dennysetiawan 2024-11-16T17:36:24Z https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1775452#M591062 <P>Dear Expert,</P><P>Any suggestion about this case?</P> Tue, 19 Jan 2021 07:11:20 GMT https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1775452#M591062 dennysetiawan 2021-01-19T07:11:20Z https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1781535#M1209509 <P>I'm still looking for a solution about this case.&nbsp;</P> Wed, 10 Feb 2021 07:51:30 GMT https://community.qlik.com/t5/QlikView/Determine-beginning-balance-mutation-and-ending-balance-from/m-p/1781535#M1209509 dennysetiawan 2021-02-10T07:51:30Z