topic Re: multi year balance sheet in QlikView

multi year balance sheet

Tue, 04 Nov 2014 17:43:04 GMT

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?

Re: multi year balance sheet

Gysbert_Wassenaar — Wed, 05 Nov 2014 09:26:55 GMT

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

Re: multi year balance sheet

Wed, 05 Nov 2014 11:15:28 GMT

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.

Re: multi year balance sheet

Gysbert_Wassenaar — Wed, 05 Nov 2014 11:31:22 GMT

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

Re: multi year balance sheet

Sat, 08 Nov 2014 08:12:20 GMT

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.

Re: multi year balance sheet

Gysbert_Wassenaar — Sat, 08 Nov 2014 17:41:49 GMT

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