Tutorial - Using Common Date Dimensions and Shared Calendars

[rwunderlich]

The question of aggregating multiple dates into a common date dimension like Month comes up frequently on this forum. I've posted a QVW tutorial on my website that shows how I typicaly handle the issue.

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions

The QVW is not Personal Edition enabled.

-Rob

Message was edited by: Rob Wunderlich Link updated

[sreenivas]

Hi Femi

Check it out

[ankitg4688]

Thanks for sharing this. I have a question here. What if there is one field from Dimension table which is common in all 3 fact tables? I am getting a loop because all 3 fact tables are linked to "Datelink" table and they are also linked to dimension table with common field. Could you please tell me how can I resolve such issue? Thanks.

[hic]

The Datelink table should be linked to one key only - in Rob's case it is the OrderID. In your case it may be that you need to find another key which is better. It should be the one with the lowest grain, possibly linking the three fact tables. See Canonical Date.

Further, if you have several fact tables, you could probably concatenate them, thereby creating one big table, thus avoiding the loop. See Concatenate vs Link Table

HIC

[ankitg4688]

Hi Henric,

Thanks for quick response. I will elaborate my scenario.

I have 3 fact tables as mentioned below.

1. F1 with 5 Million rows ( Date column A1, Common Dimension field B1 from dimension table which is repeated).

2. F2 as 60 Million rows. ( Date column A2, Common Dimension field B1 from dimension table which is repeated).

3. F3 with 70 Million rows ( Date column A3, Common Dimension field B1 from dimension table which is repeated)

4. Dimension table with B1 as primary key.

5. 5 more dimension table linked to above dimension table by other keys.

I want to roll generalise time (Year, Month, Quarter) in Dashboard, which i can get from above scenario. But, my dimension field B1 will create circular loop with 3 fact tables, which you said I should concatenate to avoid.

My Questions:-

1. If I concatenate all fact tables then must have common columns should be?

2. There will be lot of null values coming up for so many columns in my centralised fact. How can I handle it to show correct values in dashboard?

3. Will these create garbage data due to association joins in data model?

Thanks allot for your help.

[Anonymous]

Thank you very much!

I'm new to QV, am creating my first model from SAP and, confronted to multiple dates, I've found your tutorial clear and perfect!

[#7]

Hi Rob

My name is Shideh and i am from Germany. Sorry for my bad English.

We have master Calendar with week number (1, 2,. ,53)

In 2015 we have week number (53) from 28^Th2015 to 03^Th 2016.

In Set Analysis:

sales_Year:

sum({<Year={$(vYear)}, >} Sales)

sales_lastYear:

sum({<Year={$(vLastyear)}, >} Sales)

If i selected to year 2015 and week 53 it doesn’t any value for last year.

I would like compare week 53 of 2015 with week 1 of 2015.

We have the same problem in Year:

2020

2026

2032

2037

2043

2054

2060

Thanks