How to create time dimension and link to master calender

[#1]

I would like to create a time dimension and link to master calender. I'm welcome your ideas.

[swuehl]

There are tons of threads here in the forum that discusses the concept of a master calendar, also lots of sample app to create such a master calendar.

For example

http://community.qlik.com/qlikviews/1001

but just search the forum 'master calendar' for more.

So, the only problem should be to link your field to the master calendar. QV will link fields with same name.

Just take care that an actual link will only happen to records in the two tables where key field values are equal.

[#3]

You are correct, there are lot of dicussions are around Master Calender but not on time dimension and link the time dimension to master calender.

Thanks.

[swuehl]

As said, links will be created by equality of field values. So, to succesfully link a time dimension to a master calendar that is based on dates, you need to strip of your time part from your time dimension:

LOAD

DATETIME,

Daystart(DATETIME) as DATE

FROM YOURTABLE;

LOAD

DATE,

MONTH,

YEAR

FROM MASTERCAL;

[#5]

I did not understand where are you linking your master calender to time dimention. I have successfully created master calender but I'm having difficulty in create a time dimension and link to master calender.

Thanks.

[#6]

Use this...

Date:
LOAD
today()-recno()+1 as Date
autogenerate(today()-'2009-01-01');    //you can choose here how far you wish to go back by setting appropriate date

DateParts:
LOAD
Date as invoice_date,
Date as Date,
Year(Date) as Year,
yearname (Date, 0, 4 )   as FinancialYear,

Month(Date) as Month,
Day(Date) as Day,
Week(Date) as Week,
Weekday(Date) as WeekDay,
MonthName(Date) as monthYear
RESIDENT Date;

[swuehl]

The link is established by DATE field. Probably I am still missing your issue. Could you post your script code or a small sample app?

[#8]

Hi PurnaC10,

I also like an example app that illustrates your problem, and/or the description of the problem you're solving.

I can imagine you just need a time dimension next to your mastercalendar. In that way the Date dimension (mastercalendar) and your timedimension (new dimension) are not related to eachother but they are both related to your fact-table. This would require a time-field in your facttable that links to the time-field in your time-dimension.

In case the mastercalendar and time-dimension must be related, I think your mastercalendar will have quite a lot of records (depending on the granularity). What is your granularity? Is data viewed per hour? Per minute? Per second?

--> There may be a good reason for it... Lets have a look at your example app or further description

#edit: I saw this topic (http://community.qlik.com/thread/24869) with a clear example with timestamp included in the mastercalendar (so apparently it is not that strange to do...)

[#9]

here is my application and I have been trying to achieve

1. Create Master Calender and Tie with Time with granularity  of 15 min interval.

                   Year, Quarter, Month, Week, Day, Hour, Min (15,30,45,60)

I'm seeing a link between the tables with link table. but some how  location and master calender values are not linking on sheet. If I select location both charts (Mech Stats and DB Sessions) are showing valus. then if I select date or month or quarter or year, selection box not keeping multiple selection.

I would like to know why data not connectiong as shown in table layout.

Thanks for your help.

[swuehl]

I think you are trying to link a field with text values only (DTTM field in table LinkTable) with a field with a QV timestamp (DTTM field in table Calendar). I believe QV will try to match the numerical value of the timestamp with the text value, which will not work.

Try using same format in both fields.