multi year balance sheet

[#1]

I try to create a multiyear balance sheet. But have some issues on the expression. My (test)data looks like this:

Table:

Load * inline

[

Account,Year,Amount

Stock,2007,100000

Stock,2008,10000

Stock,2009,-9000

Stock,2010,5000

Stock,2011,-8000

Stock,2012,11000

Stock,2013,7000

Stock,2014,-4000

];

i have a line chart with sum(amount) as a dimension and full accumulation.. that's perfect when there are no selection.

i have a pivot with =rangesum(above(sum(Amount),0,RowNo())) ..... also perfect with no selections..

but when i select 2009 + 2010 i get the wrong data.

is there an expression to solve this?

[Gysbert_Wassenaar · Accepted solution]

Perhaps this:

sum(aggr(rangesum(above(total sum({<Year=>}Amount),0,rowno(total))),Year))

If not, maybe this document helps: Calculating rolling n-period totals, averages or other aggregations

[#3]

Works perfect!  but...... 😉

When i add a dimension i get non expected results...

Table:

Load * inline

[

Account,Year,Amount

Stock,2007,100000

Stock,2008,10000

Stock,2009,-9000

Stock,2010,5000

Stock,2011,-8000

Stock,2012,11000

Stock,2013,7000

Stock,2014,-4000

Cash,2007,25000

Cash,2008,4500

Cash,2009,-1500

Cash,2010,2500

Cash,2011,-1500

Cash,2012,2250

Cash,2013,1000

Cash,2014,-2000

];

I tried to add the dimension in the formula:

=sum(aggr(rangesum(above(total sum({<Year=,Account=>}Amount),0,rowno(total))),Year,Account))

But no luck...Any tips? I also would like to add month as a dimension in the near future.

[Gysbert_Wassenaar]

Try the AsOf table approach described in the document I linked to.

[#5]

Thanks gysbert, is that the best way to create a multiyear (or quarter) balance sheet? I mainly focusses on P&L charts and pivots, but now i want the same with the financial balance.

[Gysbert_Wassenaar]

In balance sheets and P&L charts you don't usually need to use a Year or Quarter dimension. Using expressions for each year or quarter instead is easier. For example a straight chart with one dimension and two expressions:

Dimension: Account

Expressions:

• This year: sum({<Year={'2014'}>}Amount)
• Previous year: sum({<Year={'2013'}>}Amount)

This blog post might be of interest too: How IntervalMatch Solved My Profit and Loss Dilemma