Best way to deal with two date dimensions?

[#1]

Hi all,

I have two tables sales and order. Both tables have date columns say sales date and order date . These two tables are joined using key product ID.

I want to have a date field available which would link both dates and filter the tables. For example, if I select Jan from this field, I should be able to compare order and sales amount from January.

What would be the best way to model my table to achieve this? Any help?

[#2]

I did manage to fix it by renaming the month and year fields and let qlikview form a sync table with emp id, month and year as common fields. I know this is not the best solution but it is what I have so far.

[rwunderlich · Accepted solution]

You are getting incorrect results because you are using EmpId as the key field. That is not granular enough, the EmpId appears across multiple periods. The correct Key is

[Emp ID] & [Client ID] & [R Period] as Key

That will still give you a synkey for Key and EmpId, which is logically correct, but a bit ugly.  I agree with Oleg that a concatenated Fact table is better for this case. The attached example shows both.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#4]

Thanks once again Rob