topic Inital Balance in QlikView

Inital Balance

Mon, 27 Sep 2010 18:08:30 GMT

Hi People:

I need calculate initial balance for accounts, with this structure.

The Data is:

Year	Month	Date	Account	Debit	Credit
2010	4	01/04/2010	E100	500
2010	4	09/04/2010	E100		300
2010	5	08/05/2010	E100	400
2010	5	16/05/2010	E101	120
2010	7	11/07/2010	E200	80
2010	7	19/07/2010	E100		30
2010	8	25/08/2010	E200	140

And need show ..

Year	Month	Account	beginning balance	Debit	Credit	book balance
2010	4	E100	0	500	300	200
2010	5	E100	200	520	0	720
2010	7	E100	720	0	30	690
	Total	E100	0	1020	330	690
2010	5	E101	0	120	0	120
	Total	E101	0	120	0	120
2010	7	E200	0	80	0	80
2010	8	E200	80	140	0	220
	Total	E200	0	220	0	220

	Grand Total		0	1360	330	-1690

Is possible? any idea?

Thanks! regards

César Estrada

Inital Balance

Mon, 27 Sep 2010 19:26:07 GMT

I think you should be able to use Inter-record functions to do it. Once you take the date field out, the Year and Month will be grouped, so your Debit and Credit expressions should work as is.

For Beginning Balance, it would be something like:

Above(Debit) - Above(Credit) + Above(BeginningBalance)

You may also be able to use Accumulation and have the Beginning Balance field be:

Above(Debit) - Above(Credit)

EDIT: Actually, the first expression should probably be:

Above(Debit) - Above(Credit) +
If(Above(BeginningBalance) > 0, Above(BeginningBalance), 0)

Then set your Null Symbol to 0.

Inital Balance

vgutkovsky — Mon, 27 Sep 2010 19:40:36 GMT

Cesar,

See attached solution.

Regards,

Inital Balance

Mon, 27 Sep 2010 19:43:26 GMT

Now try with this tips, Thanks!!

Inital Balance

Tue, 28 Sep 2010 16:57:32 GMT

Thanks!! work perfect!!