Link master calendar with 3 date fields from same table

[#1]

Hi All, I have an Employee having 3 DATE fields (i.e. Joining Date, Separation Date, Resignation Date). I have a master calendar created. I would like to join all three date fields with the Master calendar. I tried my best but din't succeed yet. The logic which we are trying are If I select a year from Master calendar i would like to see          1. Number of Employees hired in that year.                2. Number of employees Sperated in that year          3. Number of employees who hav applied for resignation in that year.                    if anyone can please help me with it. Its an urgent req.       Thanks to all.

[jonathandienst]

Hi

As you have already discovered, you cant link three separate dates to the same master calendar. There are several ways around this problem, but one of the simplest is to use 3 master calendars (eg CALHired, CALSep, CALRes). You will need to qualify the field names (eg MonthHired, MonthSep, MonthRes).

To display these on a chart, create a 4th calendar, not linked to any of the date fields (eg CALIsland). This is a date island dimension. Use MonthIsland  and then these expressions:

Hired:            Count(If(MonthHired = MonthIsland, EmployeeID))

Separated:      Count(If(MonthSep = MonthIsland, EmployeeID))

Resigned:     Count(If(MonthRed = MonthIsland, EmployeeID))

(adjust field names and expressions to your particular requirements)

Hope that helps

Jonathan

[#3]

Hi Jonathan, thanks for your quick reply will def try as u hav mentoined

[#4]

Hi Jonathan tried using the condition you have mentioned. As CalIsland not connected to any table or any field in the data model. but when i use   

Hired:            Count(If(MonthHired = MonthIsland, EmployeeID))

it doesn't display the count. Cna you please help me on this.

[jonathandienst]

Hi

Can you post your qvw so that we can look at it in order to help you?

Regards

Jonathan

[nstefaniuk]

Hi.

Create a pivot table with function crosstable

Fact_Table

     other attributes....,

     Autonumber(Hash128(Date1, Date2,  Date3), 'id_date') As id_date

// This one is generated by crosstable. date_type contains the name of the date field of Fact_Table

Pivot_Table

     id_date

     date_type

     date

Master_Calendar

     date