Balance Sheet Aggregation

Anonymous — Wed, 23 Nov 2011 19:22:12 GMT

I am attempting to create a financial balance sheet model and I am having difficulty in calcualting the aggregate balances. Here is what I have so far...

My Source data looks something like this... Note that there are more periods and lots more transactions from many different locations.

Date	Period	Year	JE Description	Trans Amount
2/1/11	2	2011	Beg Bal	10,000
3/1/11	3	2011	Beg Bal	11,000
4/1/11	4	2011	Beg Bal	9,500
2/10/11	2	2011	Sold	-1,000
2/20/11	2	2011	Purchased	2,000
3/10/11	3	2011	Sold	-3,000
3/20/11	3	2011	Purchased	1,500
4/10/11	4	2011	Sold	-3,500
4/20/11	4	2011	Purchased	2,000

Here is what I want to get when I select periods 2, 3 and 4 (Feb - Apr)

	1	2	3	4
	Balance	Change	History	Detailed Balance
Inventory	8,000	-2,000	8,000	0
Feb	11,000	1,000	11,000	11,000
Mar	9,500	-1,500	-1,500	9,500
Apr	8,000	-1,500	-1,500	8,000

I can calculate 2 the change as follows

num(sum( {$<Year = {$(=Max(Year))},[JE Description] -= {'Beg Bal'} >} [Transaction Amount]), '#,##0')

I can calculate 3 the history as follows

num(sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount]), '#,##0')

I can calculate 4 the detailed balances as follows (4 is not neccessary if I can get 1)

num

(
RangeSum(Above(
sum( {$<Year = {$(=Max(Year))}, Period = {$(=Min(Period))}, [JE Description]={'Beg Bal'} >} [Transaction Amount]) +
sum( {$<Year = {$(=Max(Year))}, Period = {">=$(=Min(Period))<=$(=Max(Period))"}, [JE Description] -={'Beg Bal'} >} [Transaction Amount])
, 0, RowNo()))
, '#,##0')

I can not calculate 1. My issue arises trying to calculate the aggregate. The individual periods are correct but the aggregate does not work out. Instead of 8,000 which is my closing balance for April all I can get is 28,500 which includes all beginning balances.

Balance Sheet Aggregation

Anonymous — Thu, 24 Nov 2011 21:48:58 GMT

This seems to do it...

=if(count(DISTINCT(Period))=1, sum( {$<Year = {$(=Max(Year))} >} [Transaction Amount]), sum( {$<Year = {$(=Max(Year))}, Period = {$(=Max(Period))} >} [Transaction Amount]))

Not sure if there is a better way but I will stick with this for now.

topic Balance Sheet Aggregation in QlikView

Balance Sheet Aggregation

Balance Sheet Aggregation