measure - little too complex

Last Post 07 Apr 2005 12:51 PM by kkalapan. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

07 Apr 2005 12:51 PM
Here is the layout of my Job/Employment fact:

emp_id, bgndate_id, enddate_id, dept_id,status_id,year_val(not an ID)

Each of the ID columns above is a reference to a dimension(employee, begin date, end date, department, status).

What I want to be able to do is:

count(distinct emp_id)
where bgndate <= 1/1/year_val<= enddate
and emp_status in ('VAL1','VAL2')

i.e count only people who have a record with begin and end dates spanning the begining of any year.

The bgdate, enddate I mention above in my pseudo-query would have to be obtained from the respective date dimensions.

The begin and end dates function as dates which determine duration for which any record is valid.

How would I go about buidling a measure/calculated measure to accomplish the above ?

Acceptable Use Policy