Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
ID | event_year |
---|---|
1 | 2011 |
1 | 2010 |
2 | 2011 |
2 | 2009 |
3 | 2011 |
3 | 2010 |
3 | 2009 |
tab2:
ID | pay_year | ACC |
---|---|---|
1 | 2011 | 100 |
1 | 2010 | 110 |
1 | 2010 | 120 |
1 | 2009 | 200 |
2 | 2010 | 210 |
2 | 2009 | 130 |
3 | 2011 | 150 |
3 | 2011 | 180 |
3 | 2010 | 170 |
3 | 2009 | 500 |
with the information from these 2 tables, I would like to make a pivot table that should look something like this:
event_year | 0 | 1 | 2 |
2009 | sum(ACC) for event_year & pay_year+0 | sum(ACC) for event_year & pay_year+1 | sum(ACC) for event_year & pay_year+2 |
2010 | sum(ACC) for event_year & pay_year+0 | sum(ACC) for event_year & pay_year+1 | - |
2011 | sum(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_year | axa_y | 0 | 1 | 2 |
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.
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'