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?

[swuehl]

Here are some useful links:

Why You sometimes should Load a Master Table several times

Canonical Date‌

Tutorial - Using Common Date Dimensions and Shared Calendars

[#3]

Thank you for giving the links, I will go through them. Meanwhile, if anyone has already implemented this please share your solution.

[rwunderlich]

The links swuehl provided do provide solutions. The last one provides a working qvw example that matches your case.

-Rob

[#5]

Hi Rob

Excellent tutorial. I tried to implement the same and am seeing some issues can you please suggest what would be the problem?

The table which says month is not breaking down the values accordingly. Sample application attached

[Oleg_Troyansky]

Hi Praveen,

you have two fact tables - Sales and Order, which you linked by Product ID. In reality, these two tables should have more common dimensions. For example, Customer - both Sales and Orders tables should have a customer dimension. Date dimension, that you mentioned, is also common. There might be more, depending on your business scenario - shipping address, delivery method, etc.

The best possible way of working with two Fact tables like these two, is to concatenate the Facts into a Single Fact data model. This method also involves the process of finding and restoring missing associations - those associations that existed when you linked the two tables, and disappeared when you concatenated them.

As a result of concatenating the two Fact tables, the two Date fields naturally converge into a single Date field that can be linked to a single Master Calendar.

I'm describing this process in detail in my new book, QlikView Your Business. Check it out!.

Cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[#7]

I think the issue is with how we form the key. In the tutorial, order ID doesn't repeat more than once.

In my example, emp id repeats. I don't have a unique field to identify every row.How do I remodel my fact tables ?

[Oleg_Troyansky]

You won't have these issues if you follow my advise and concatenate the two tables.

[Anonymous]

Oleg,

Please, do tell how it would be possible to recreate the lost associations with the dimensional data once the two fact tables are concatenated. Would we need to reload those dimensions/call them from a resident load and left join? Or, even simply have a linking key to re-associate the lost relations?

Thanks,

Ron

[#10]

Can you please help with the sample file I provided?