topic Re: Table Chart Year / Previous Year Set Analysis in QlikView

Table Chart Year / Previous Year Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table Chart Year / Previous Year Set Analysis

sunny_talwar — Wed, 30 Mar 2016 11:10:10 GMT

Try this expression:

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

Re: Table Chart Year / Previous Year Set Analysis

Anonymous — Wed, 30 Mar 2016 11:43:46 GMT

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

Re: Table Chart Year / Previous Year Set Analysis

sunny_talwar — Wed, 30 Mar 2016 12:49:29 GMT

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

Re: Table Chart Year / Previous Year Set Analysis

Wed, 30 Mar 2016 13:25:30 GMT

Maybe you should do this inside the script:

table1:

load * inline [

Year, amount

2010, 100

2011, 10

2012, 200

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;

Re: Table Chart Year / Previous Year Set Analysis

Wed, 30 Mar 2016 13:40:23 GMT

table1:

load * inline [

Year, amount

2010, 100

2011, 10

2012, 200

2013, 20

];

table2:

NoConcatenate

LOAD

Year + 1 AS Year,

SUM(amount) AS amount_last_year

Resident table1

Group By Year;

Re: Table Chart Year / Previous Year Set Analysis

maximiliano_vel — Wed, 30 Mar 2016 18:00:12 GMT

What is the easy way?

Re: Table Chart Year / Previous Year Set Analysis

sunny_talwar — Wed, 30 Mar 2016 18:08:09 GMT

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

But this only works in QV12

Re: Table Chart Year / Previous Year Set Analysis

maximiliano_vel — Wed, 30 Mar 2016 18:09:40 GMT

Thanks