Set analysis: use value of other dimension

[wim_rijken]

I have a very simple data model:

Product

Posting Date

Posting Month (derived from Posting Date)

Posting Year (derived from Posting Date)

Document Date

Document Month (derived from Document Date)

Document Year (derived from Document Date)

Amount

I would like to create a Pivot table:

• rows: product
• columns: Posting Year and Posting Month
• expressions:
  • Amount based on Posting Year/Month > this is simple, just add the expression: sum(Amount)
  • Amount based on Document Month > what is the formula for this?

So something like: Sum({$<Posting Year=,Document Year={Posting Year}>} _Amount)

Do I need set analysis for this, or can I achieve this in another way?

[swuehl]

Create a canonical date from Posting date and Document date:

Canonical Date

Create a calendar for that canonical date (Year and Month)

Filter for date type (document / posting) in your expressions.