Set Analysis Prob

[#1]

I do have the following dataset:

Sales

     -Sales Amount

     -Sales Year

Invoice

     -Invoice Amount

     -Invoice Year

I want to create the following table:

                              2012     2011     2010     2009

Sales Amount          28          33       25          5

Invoice Amount         33          28       26         9

Sales and Invoices are linked by an ID. But I want the sales and invoice of that specific year. Based on Invoice Year and Sales Year.

How can I solve this?

[#2]

no reference due to problems with the model

no big new table as 3 Anwers above

Another try:

One small new table:

QUALIFY *;

Fact:

LOAD Sum(Sales Amount) as Sales Amount,

  Sales Year as Year

RESIDENT Sales

GROUP BY Year

JOIN

LOAD Sum(Invoice Amount) as Invoice Amount,

Invoice Year as Year

RESIDENT Invoice;

GROUP BY Year

UNQUALIFY *;

Has only 2*Count(Distinct Year) Entries.

Small enough?

Greets

Klaus