topic Re: Tutorial - Using Common Date Dimensions and Shared Calendars in QlikView

Tutorial - Using Common Date Dimensions and Shared Calendars

rwunderlich — Tue, 20 Nov 2012 20:30:44 GMT

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

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Wed, 21 Nov 2012 08:59:44 GMT

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

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

rwunderlich — Wed, 21 Nov 2012 16:53:20 GMT

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

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Sat, 24 Nov 2012 09:06:56 GMT

Hi Rob Wunderlich

Can you kindly send PE Ed enabled QVW?

Many Many thanks in advance!

Shoaib

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Sat, 24 Nov 2012 23:19:48 GMT

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

HIC

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

rwunderlich — Sun, 25 Nov 2012 02:35:16 GMT

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

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Sun, 25 Nov 2012 03:10:45 GMT

Thanks a lot Mr Henric and Rob!

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Sun, 25 Nov 2012 12:28:55 GMT

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

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Fri, 14 Jun 2013 00:08:09 GMT

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.

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Fri, 14 Jun 2013 04:18:52 GMT

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.

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Fri, 14 Jun 2013 04:26:23 GMT

Pls refer the datamodel, both the date cols. are in database table, have tried to club both in seperate table named as Duedate

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Fri, 14 Jun 2013 07:36:30 GMT

You need to solve this by creating a link between the common date and the FILE_NO, so that a FILE_NO can have several dates. I would make a bridge table like in the following picture

where each line corresponds to either an Action date, or a Due date, or some other date. Then it is just a matter of linking this to the rest of the data through FILE_NO:

I am not sure that your current COMMON_INFO table does the above.

HIC

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Fri, 14 Jun 2013 08:10:52 GMT

Thanks Henric for the help, will work on it i have used the traditional way of common calender, will let u know the results in short time,

also need a small favour,

hw to manage the Tilldate concept, whn user selects Duedate, he shld get the data in the chart till that date selected.

Thanks again

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Fri, 14 Jun 2013 08:15:40 GMT

Set analysis. E.g.

Sum({$<OrderDate={"<=$(=Max(Duedate))"}>} Sales)

HIC

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Fri, 14 Jun 2013 21:17:06 GMT

Hi Henric,

I can understand the link table consept, and theriticalley it should work. But if you look at this this model, why the month field in Table 3 was not connecting to the rest of the data in Table 1 and Table 2.

Thanks for your help.

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

hic — Tue, 18 Jun 2013 09:43:07 GMT

Because your data isn't matching. If I select all values in Key1, then all values in Key2 and Key3 are excluded. And the same is true whichever key I use.

HIC

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

Thu, 20 Jun 2013 19:06:45 GMT

so if i understand, the key data need to be match to link the data across the objects. Not the data element like the one on this picture. If that is correct, what would be the solution on how to connect this data.

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

darren_dixon — Fri, 26 Jul 2013 11:20:38 GMT

Hi,

Note - I've attached my QVW of the problem

I am trying to use the common calendar example linked here:

http://community.qlik.com/thread/66717

My problem is that the appointments are not adding to the table.

The total number of appointments should be 236779

The actual figures should look like this:

Month	Appointments
Total	236779
Dec	13519
Aug	13698
Sep	14630
Feb	16087
Nov	17336
Mar	17494
Jan	18179
Oct	18240
Jul	18791
Jun	26561
Apr	29237
May	33007

When I change the date to date(ActualStartDate) i get the following:

Month	sum( {<DateType={Order}>} OrderCounter)	sum( {<DateType={Shipment}>} ShipmentCounter)	sum( {<DateType={Order}>} Quantity)	Appointments	Travel Time
Total	6	11	621	236779	1959:03:00
Jan	2	1	203	0	0:00:00
Feb	1	3	103	0	0:00:00
Mar	3	0	315	0	0:00:00
Apr	0	1	0	0	0:00:00
Jun	0	1	0	0	0:00:00
Jul	0	0	0	0	1959:03:00
Aug	0	1	0	0	0:00:00
Sep	0	1	0	0	0:00:00
Oct	0	1	0	0	0:00:00
Nov	0	1	0	0	0:00:00
Dec	0	1	0	0	0:00:00
-	0	0	0	236779	0:00:00

When I change the date to Date(Floor(ActualStartDate) I get the following:

Month	sum( {<DateType={Order}>} OrderCounter)	sum( {<DateType={Shipment}>} ShipmentCounter)	sum( {<DateType={Order}>} Quantity)	Appointments	Travel Time
Total	6	11	621	236779	1959:03:00
Jan	2	1	203	219481	0:00:00
Feb	1	3	103	219750	0:00:00
Mar	3	0	315	233238	0:00:00
Apr	0	1	0	230957	0:00:00
May	0	0	0	231043	0:00:00
Jun	0	1	0	227118	0:00:00
Jul	0	0	0	233309	1959:03:00
Aug	0	1	0	221531	0:00:00
Sep	0	1	0	222419	0:00:00
Oct	0	1	0	223157	0:00:00
Nov	0	1	0	226379	0:00:00
Dec	0	1	0	222079	0:00:00

Thanks,

Darren

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

rwunderlich — Fri, 26 Jul 2013 12:08:11 GMT

You appear ro have some expressions mixed in from the example as well as your data. Are there really related? Please post a sample QVW or your table viewer picture.

In your last comment, it appears that your ActualStartDate field is a timestamp and you need to floor() it to get the integer portion. to make a linkage to the Calendar. This is correct. I would have to see your data model to explan the double counting, but I wouuld start by removeing the sameple expressions from the table.

-Rob

Re: Tutorial - Using Common Date Dimensions and Shared Calendars

darren_dixon — Fri, 26 Jul 2013 12:14:58 GMT

Hi Rob,

I posted this earlier today. I'm unsure how to attached the QVW to this post so I've included it here: http://community.qlik.com/thread/87958?tstart=0