# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

New Contributor III

## Balance Sheet Aggregation

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.

Tags (2)