Date Dimension Question

[Anonymous]

Good day,

I've read up on the Date dimension articles, DATE LINK tables etc, however it does not really answer a question I have.

I am trying to compare summaries from two transaction tables.

Each transaction table has a transaction date and the transactions occur for a specific BRANCH.

In a chart I would like to have the BRANCH and DATE (Year, Month, Day) as dimensions and two summaries from each transaction table in the expressions list.

I can link a date dimension to both transaction tables, however since these two tables are also linked to the "BRANCH" table it creates circular references.  Both need to link to the branches and to dimension each table on the dates as well each table must reference the same Date dimension also, which makes creating separate date dimensions not possible in this scenario.

I also do not have a common key between the two transaction tables... So I can not create a DATE_LINK table.

Any ideas ?

Ideally I do not want the user to have to select two comparison months.

The pivot chart should display SUM differences per month if any exist between the two transaction tables..

Kind regards

Edwin

[Anonymous]

Did you tried creating the master calendar?

If not create the one and link one of the dates to this and you can play around with other things

[alexandros17]

Instead of linking the 2 tables with a third "period" table  try to left join the period table to both tables, in this case you will have 2 table both with branches and period selectors (year, month ...) with a synthetic key.

Let me know

[jvishnuram]

Hi Edwin,

You can make a composite key like BRANCH-DATE(Cape Town-22-May-14), so that you can create a common link between two tables and also you can avoid the circular reference.

Use this code in script

BRANCH_NAME&'-'&Date(TRANSACTION_DATE,'DD-MMM-YY') as KEY

Then link any one of the table to calendar and you can get your required output through the link.

[Anonymous]

Yes I have tried to create a MasterCalendar.  Both transaction tables need to link to the MasterCalendar and that in itself works.... however the problem is created when both transaction tables need to link to a branch table as well...