Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Pivot table, cannot determine a simple function based on years dimension

Hello all!

I need your help urgently, so every small information will be usefull for me.

I have 2 tables that I am loading.

tab1:

IDevent_year

1

2011
12010
22011
22009
32011
32010
32009

tab2:

IDpay_yearACC
12011100
12010110
12010120
12009200
22010210
22009130
32011150
32011180
32010170
32009500

with the information from these 2 tables, I would like to make a pivot table that should look something like this:

event_year012
2009sum(ACC) for event_year & pay_year+0sum(ACC) for event_year & pay_year+1sum(ACC) for event_year & pay_year+2
2010sum(ACC) for event_year & pay_year+0sum(ACC) for event_year & pay_year+1-
2011sum(ACC) for event_year & pay_year+0--

*the 0,1,2 values that are added to year3, belong from the table header.

I don't know how to make an expression for this...I searched all over the community, but no result.

To make a better idea of what I am searching for, maybe this table will explain:

event_yearaxa_y012
2009

sum(ACC) refering to

2009-as pay year

sum(ACC) refering to

2010-as pay year

sum(ACC) refering to

2011-as pay year
2010

sum(ACC) refering to

2010-as pay year

sum(ACC) refering to

2011-as pay year

-
2011

sum(ACC) refering to

2011-as pay year

--

So:-the first collumn (0) must show all the ACC that are done and paid in the same year

     -the second collumn(1) must show all the ACC that are done in event_year and paid in the next year

     -the third collumn(2) must show all the ACC that are done in event_year and paid 2 years further.

What I have tried so far, but garbadge result:

I have created an internal table somehow to add the 'axa_y' to the year field....I don't know if this can be a solution...

axa_y:

LOAD * INLINE [

axa_y

0

1

2

];


after that I have created a pivot table with the dimensions 'event_year' and 'axa_y' and at the expression I have the following:

'=if(pay_year=event_year+axa_y , sum( ACC))'

but still no result...

Please help me because it's urgent, and sorry my english in this post.

Thank you very much,

Silv.

1 Reply
Not applicable

Pivot table, cannot determine a simple function based on years dimension

A small completation for a better understanding

the calculation fields must show something like this (but I don't know how to put this in the expression):

sum(ACC for 'event_year', 'event_year' + 'axa_y')

where 'event_year' + 'axa_y' is refering to 'pay_year'

Community Browser