1 Reply Latest reply: Jan 12, 2012 6:17 AM by silviu andries RSS

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

    silviu andries

      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.