Linking with Master calender

[#1]

Hi all,

There are many date fields columns in my application. So i have created the master calender. Major problem for me is to connect the master calender . So as to created the month,year and other dates field I have generated the master calender to date selection.I am not able to connect the table with master calender. Attached the word doc for your reference.

Since the master calender has only one date field. how to create from and to .Do i need to add one more column ?

[#2]

Hi

Tell me which is the key field in the table. If "opendate" is the key field then you rename that field as "Date" or else whatever is the key field rename it as "Date".

Hope it has helped you.

[Miguel_Angel_Baeyens · Accepted solution]

As you have several dates in your table, it depends on what of those datas should be considered "Master", and you're likely to need more than one calendar, one for each date you want to analyze.

Say you want to display in a chart the evolution yearly, weekly and monthly of calls pending. Then you will need to create a calendar based on "pendingdate". If you want to display a chart by month, week and day of week for "closeddate" too, you will need to create another calendar based on "closeddate".

So you will have one month listbox for "pendindate" and another independent month listbox for "closeddate" in the same sheet, as the dates mean different events for one given fact.

The master calendar will be based on the main date on your fact table, and that depends on the requirements of that projet and the way you want to show the data.

Hope this helps.

[Miguel_Angel_Baeyens]

An example with dummy data would be (check syntax, there maybe some typos)

Data:LOAD Ceil(Rand() * 100) AS callno, Date('01/01/2010') + Ceil(Rand() * 30) AS pendingdate, Date('01/01/2010') + Ceil(Rand() * 30) AS closeddateAUTOGENERATE 100; PendingMinMax:LOAD Max(pendingdate) AS pendMaxDate, Min(pendingdate) AS pendMinDateRESIDENT Data; LET vMinPendDate = Peek('pendMinDate', 0);LET vMaxPendDate = Peek('pendMaxDate', 0); PendingCalendar:LOAD Date($(vMinPendDate) + IterNo() - 1) AS pendingdate, Month(Date($(vMinPendDate) + IterNo() - 1)) AS pendingdate_month, Week(Date($(vMinPendDate) + IterNo() - 1)) AS pendingdate_weekAUTOGENERATE 1 WHILE $(vMinPendDate) + IterNo() -1 <= $(vMaxPendDate); ClosedMinMax:LOAD Max(closeddate) AS closedMaxDate, Min(closeddate) AS closedMinDateRESIDENT Data; LET vMinClosedDate = Peek('closedMinDate', 0);LET vMaxClosedDate = Peek('closedMaxDate', 0); ClosedCalendar:LOAD Date($(vMinClosedDate) + IterNo() - 1) AS closeddate, Month(Date($(vMinClosedDate) + IterNo() - 1)) AS closeddate_month, Week(Date($(vMinClosedDate) + IterNo() - 1)) AS closeddate_week, WeekDay(Date($(vMinClosedDate) + IterNo() - 1)) AS closeddate_weekdayAUTOGENERATE 1 WHILE $(vMinClosedDate) + IterNo() -1 <= $(vMaxClosedDate); DROP TABLE ClosedMinMax;DROP TABLE PendingMinMax;

Hope that helps.

[#5]

First i want to thank you very much giving solution in different dimensions.

As you said analysis will be done mainly on two date fields Opendate and closeddate like yearly,Quaterly,monthly,weekly ,daily and hourly.

Ideally if we create two master calendar and linking to data table and adding additional columns in the data table like year,Quarter,month,... will give the same result. Is there will be any performance issues??????

Since we will not be able to create the two calender objects with different date. for ex. if the opendate field is used to create From and To Calender object.Then we cant use this for the range selection. How to overcome this?

Suppose if we want to create a trend chart which shows both open calls and closed calls of the same duration consider Jan-2009 to Dec-2009. Is it possible to do the same with above scenarios where both dates are two different columns.

[Miguel_Angel_Baeyens]

There shouldn't be any performance issues. You are creating two tables instead of just one.

XXX wrote:Since we will not be able to create the two calender objects with different date. for ex. if the opendate field is used to create From and To Calender object.Then we cant use this for the range selection. How to overcome this?

There are several ways I think of. Use a variable with the calendar object. You can use macros to pass the values on two different date fields, for example. Using OnSelect / OnChange triggers will help to keep charts updated based on user selections.

Hope that helps.