Table Chart Year / Previous Year Set Analysis

[Anonymous]

Hi,

Can anybody help here.

I need to display the sum of the year and previous year on one row in a table chart but I can't figure it out.

The chart should look like:

2010	200	0
2011	20	200
2012	400	20
2013	40	400

thx

[sunny_talwar]

Try this expression:

If(Sum(amount) > 0, Alt(Above(Sum({<Year>}amount)), 0))

[Anonymous]

Hi,

Thx for the feedback.

This is working except if the customer want's to change the sorting of one of the columns.

It should really take the year -1 to be sure.

Br,

Mario

[sunny_talwar]

Are you using QV12? If yes, then there is a easy way to fix that in such a way that user's change in sort order won't have any impact on the use of Above function.

Alternatively, you can create an AsOf Table: The As-Of Table

[#5]

Maybe you should do this inside the script:

table1:

load * inline [

Year,  amount

2010,  100

2010,  100

2011,  10

2011,  10

2012,  200

2012,  200

2013,  20

2013,  20

];

table2:

NoConcatenate

LOAD

    Year,

    SUM(amount_last_year) AS amount_last_year

Group By Year;

LOAD

    Year + 1 AS Year,

    amount,

    IF(isnull(peek('Year')), 0,

        IF(peek('Year') = Year, amount + peek('amount_last_year'), amount)

        )  AS amount_last_year

Resident table1

Order By Year asc;

[#6]

Or

table1:

load * inline [

Year,  amount

2010,  100

2010,  100

2011,  10

2011,  10

2012,  200

2012,  200

2013,  20

2013,  20

];

table2:

NoConcatenate

LOAD

    Year + 1 AS Year,

    SUM(amount) AS amount_last_year

Resident table1

Group By Year;

[maximiliano_vel]

What is the easy way?

[sunny_talwar]

Easy way is to use the sort-able Aggr() function: The sortable Aggr function is finally here!

But this only works in QV12

[maximiliano_vel]

Thanks