topic Link master calendar with 3 date fields from same table in QlikView

Link master calendar with 3 date fields from same table

Mon, 09 Apr 2012 15:03:13 GMT

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.

Link master calendar with 3 date fields from same table

jonathandienst — Mon, 09 Apr 2012 15:40:21 GMT

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

Link master calendar with 3 date fields from same table

Mon, 09 Apr 2012 16:28:20 GMT

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

Link master calendar with 3 date fields from same table

Tue, 10 Apr 2012 05:47:13 GMT

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.

Link master calendar with 3 date fields from same table

jonathandienst — Wed, 11 Apr 2012 20:02:43 GMT

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

Regards

Jonathan

Link master calendar with 3 date fields from same table

nstefaniuk — Wed, 11 Apr 2012 21:08:06 GMT

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