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

[hic]

The solution that you suggest is from a data modelling perspective a very good one. So I can only recommend people to use it, if they really want a common date field for all dates in the data model.

But I am more interested in the question whether you should have a common date field in the first place. There are some advantages, e.g. it is possible to make graphs with a generic date as x-axis. But there are also drawbacks:

• It is confusing for a user that is well aquainted with the dates in the DB.
• It is not possible to make selections like "OrderMonth='Sept' and DeliveryMonth='Dec'"

This is why I claim that it is important that the data model have several "master" calendars, one per date. Otherwise proficient users will not be able to make the selections they want. See more in my blog post.

I think that many app developers (that come from the DB world) have a mind set that the correct solution can only have one Master Calendar and therefore they do not investigate alternatives.

The good side is that you can have both. A common date dimension does not rule out separate date dimensions. So my advice is: Do both!

HIC

[rwunderlich]

Thanks for your input Henric. I agree that it's frequently useful to create several calendars in the model. In spite of it's title, the tutorial shows how to implement seperate calendars in addtion to a common calendar. The tutorial should be useful for those wanting common calendars, seperate calendars or both. The final model in the tutorial looks like this.

-Rob

[#4]

Hi Rob Wunderlich

Can you kindly send PE Ed enabled QVW?

Many Many thanks in advance!

BR

Shoaib

[hic]

I have now embedded a license so the file should work on a Personal Edition. Download here.

HIC

[rwunderlich]

Henric,

Thank you for PE enabling the tutorial. Would you mind re-saving so it opens to the Introduction sheet? I've updated the copy on my site http://robwunderlich.com/tutorials/ to have an on-open action for that sheet.

-Rob

[#7]

Thanks a lot Mr Henric and Rob!

[hic]

You can change the UI in the file any way you want to. It will not invalidate the embedded license. The license will be invalidated by changes in the data model though.

Further, the embedded license is time limited until Jan 31 (I think).That is the only embeddable license I have at the moment.

HIC

[#9]

First thank you both Mr Henric and Rob. I went through your tutorial and its very valuable especially people like me. But I want to bring up the situation to your attention. The above tutorial  is true if we have common column on all objects or table. But in real, if there is an object or objects does not have a common columns and have date field and want to connect these date fields to a common calendar. In Q how do you handle this situation.

Thank you very much for your help.

[#10]

Thanks for the tutorial Rob and Henrick,

in addition to this,

i have a different scenario,

i have 6 tables whr common calender is already defined, (Commondate)

now in first table thr r 2 more dates available Action date and Email date

now the user demands is, club both the dates and term it as duedate, and moreover user wants selection on this duedate as if to see the records in the chart till the duedate selected,

is this possible i have tried a lot to solve this, Qtn is unanswered on community since 1 week..

pls share some directions /inputs on this.