Master Calendar with Multiple Dates

[microwin88x]

Hello!

I have 3 tables in my data model where all the tables are associated by ACCOUNT_ID.

Also, I have dates:

Table A: ACCOUNT_ID, DATE_PLANNED

Table B: ACCOUNT_ID, DATE_CANCELLED

Table C: ACCOUNT_ID, DATE_TESTED, DATE_SENT

What I need is to create a bar chart to see:

X axis: The monthly periods (January, February, etc)

Y axis: Quantity of Accounts for every month differentiated if they where planned, cancelled, tested or sent.

How could I do this?

Thank you!!!

[#2 · Accepted solution]

• Create a Link Table,like

Link:

Load ACCOUNT_ID, DATE_PLANNED

Resident TableA;

Join

Load ACCOUNT_ID, DATE_CANCELLED

Resident TableB;

Join

Load ACCOUNT_ID, DATE_TESTED, DATE_SENT

Resident TableC;

• Do a Resident load of Link to add RowNo() as Date_Key
• Now create a new MasterDateTable like

Load Date_Key,

DATE_PLANNED as Date,

'Planned' as Type

Resident Link;

Load Date_Key,

DATE_CANCELLED as Date,

'Cancelled' as Type

Resident Link;

Load Date_Key,

DATE_TESTED as Date,

'Tested' as Type

Resident Link;

Load Date_Key,

DATE_SENT as Date,

'Sent' as Type

Resident Link;

These four tables will now be auto concatenated and using the Status you can control your expressions on the front-end and use Date as Dimension

Thanks

AJ

[microwin88x]

I like your solution, but the thing is that I get synthetic keys. Maybe I should drop some of those tables?

Because my model has the following tables:

Table A

ACCOUNT_ID

DATE_PLANNED

Table B

ACCOUNT_ID

DATE_CANCELLED

Table C

ACCOUNT_ID

DATE_TESTED

DATE_SENT

And I will be adding the following tables:

Link Table

ACCOUNT_ID

DATE_PLANNED

DATE_CANCELLED

DATE_TESTED

DATE_SENT

ROWNO() AS DATE_KEY

MasterDate Table

DATE_KEY

DATE

TYPE

[#4]

Oops forgot to mention that. Yes you would have to drop them in the original tables and have them only in the Link table

Thanks

AJ