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

[#2]

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

[hic]

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

[#4]

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

[hic]

Set analysis. E.g.

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

HIC

[#6]

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.

[hic]

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

[#8]

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.

[darren_dixon]

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

[rwunderlich]

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

[darren_dixon]

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