topic Re: Opening and Closing Balance in QlikView

Opening and Closing Balance

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a big problem and I bet someone can give me a hint for it.

I need to get the opening balance and closing balance for each year.

I am calculating the balance as

=RangeSum(Above(Total Sum(Debit) - Sum(Credit),0,RowNo(TOTAL)))

But when i am selecting 2013 , it is not carrying Dec 2012 balance.

i am attaching the raw data.

Can anyone help me please?

Re: Opening and Closing Balance

sunny_talwar — Wed, 07 Feb 2018 02:18:49 GMT

‌May be you need this

=RangeSum(Above(Total Sum({<Year>}Debit) - Sum({<Year>} Credit),0,RowNo(TOTAL))) * Avg(1)

Re: Opening and Closing Balance

sunny_talwar — Wed, 07 Feb 2018 13:04:07 GMT

Or this

=RangeSum(Above(Total Sum({<Year, Month, Date>}Debit) - Sum({<Year, Month, Date>} Credit),0,RowNo(TOTAL))) * Avg(1)

Re: Opening and Closing Balance

Anonymous — Wed, 07 Feb 2018 14:27:20 GMT

Thank you very much Sunny for your time. This solution works for me

Re: Opening and Closing Balance

ajayagraa — Wed, 08 Aug 2018 06:30:16 GMT

Hi Sunny

I have also slimier kind of issue can you please help me on this

Hi All

In one of the requirement I have to show opening and closing emp headcount on month on month basis in my dashboard . also I have to develop the water fall chart to show the current Month and current year opening and closing Emp headcount with hiring and leaving of that month . am using master calendar and on selection of that the chart will filter out accordingly . formula to calculate the opening and closing are given below

Opening balance = closing of the last month

Hiring = Current month joining

Leaving = Current month Leaving

Closing - Opening + Joining -leaving

can someone help me to achieve this requirement

Month	Opening Balance	Hiring	Leaving	Closing
Apr-17	1234	10	5	1239
May-17	1239	5	4	1240
Jun-17	1240	20	10	1250
Jul-17	1250	25	4	1271
Aug-17	1271	20	10	1281
Sep-17	1281	10	10	1281
Oct-17	1281	40	20	1301
Nov-17	1301	10	5	1306
Dec-17	1306	25	8	1323
Jan-18	1323	32	12	1343
Feb-18	1343	12	6	1349
Mar-18	1349	28	20	1357
Apr-18	1357	5	20	1342
May-18	1342	9	5	1346
Jun-18	1346	0	0	1346

Re: Opening and Closing Balance

sunny_talwar — Wed, 08 Aug 2018 12:01:39 GMT

Responded to your post here

How can we show opening and closing Head count MOM

Re: Opening and Closing Balance

zkazimov — Mon, 30 Dec 2019 03:18:50 GMT

Hi Sunny,

Can you explain more what avg(1) does to the calculation?

Also, when using this measure on Line Chart and filtering year =2019, I able to show starting balance for 2019 from 2018, but all prior years dimension is left on the x axis and data starts from 2019. How can i get rid of prior years from x axis? Image attached for your reference.

Thank you very much.