Get beginning and ending balance

[cristianj23a]

Hello, I want to obtain the beginning and ending balance from January 2020.

Logic:

Initial Balance: In January 2020 it is 48,000 but in February 2020 it must be the result of the final balance of January 2020 and so on.

Final Balance: in the first month it must be the subtraction of charges and credits plus the initial balance, in February 2020 it must also be the difference of charges and credits but also adding the initial balance which is the final balance of the previous month.

Please help me with this issue that I get stuck, I attach the logic in excel. Greetings.

[Dalton_Ruer]

Refer to this post that is about stock movement. Very similar concepts. 

The key concepts you can search for are Master Calendar and the function Peek

https://community.qlik.com/t5/App-Development/Historical-Stock-Level-from-Current-Stock-and-Transact...

[cristianj23a]

Hello Dalton, it happens that in this case the initial balance is in the first month and from there it makes the calculation with charges and credits, in the excel there is the logic that I cannot replicate it in qlik.

Greetings.

[MayilVahanan]

Hi @cristianj23a 

Try like below, I hope that, you've initial balance is on first row only.

You can change inline to ur source.

LOAD Año, Mes,charges, credits, Num(if(Len(Trim([Initial Balance]))>0, [Initial Balance], Peek([Final Balance])),'#,##0.##') as [Initial Balance],Num(rangemax([Initial Balance],Peek([Final Balance]))+charges-credits,'#,##0.##') as [Final Balance] INLINE [
Año, Mes, Initial Balance, charges, credits
2020, Ene, "48,000.00", "6,245,506.70", "5,302,231.19"
2020, Feb, , "9,286,148.91", "10,201,374.70"
2020, Mar, , "17,593,646.67", "15,921,837.61"
2020, Abr, , "12,363,681.35", "13,148,040.38"
2020, May, , "16,632,009.03", "15,985,346.85"
2020, Jun, , "9,895,502.25", "8,249,305.10"
2020, Jul, , "23,708,003.23", "26,897,260.73"
2020, Ago, , "69,784,162.45", "68,430,859.22"
2020, Sep, , "47,554,388.27", "48,352,336.50"
2020, Oct, , "69,152,846.19", "69,239,854.05"
2020, Nov, , "100,173,781.32", "100,433,565.37"
2020, Dic, , "116,092,252.30", "115,475,730.38"
2021, Ene, , "119,382,846.93", "118,708,489.89"
2021, Feb, , "92,393,881.52", "93,609,837.07"
2021, Mar, , "250,245,275.70", "249,842,968.96"
2021, Abr, , "96,725,773.23", "97,455,318.20"
];

[MayilVahanan]

Missed the output, FYR

O/P: