I need to calculate 2nd year retention for Employees

Question

We have an EmployeeEvents cube (multidimensional). I need to get a set of employees that started anytime in the last 24 months (I have a new hire flag). The count of this set is the denominator. Then get a subset of those employees that are still at the company (numerator, regardless if they moved around in the company (same or different departments. One more caveat, if the employee quit for two specific reasons during the 2 year peiod, then they should be excluded from both sets. The final part is that they want this ratio as a built in measure. I started by creating the mdx. It sort of works but only for a single date. When I use a range of dates in the "where " it breaks complaining the CurrentMember is a set. Below is the code that I have so far. It kind of works but I'm being told that Measures.[2ndYearRetention] must be a built in measure on the cube. Any help is greatly appreciated.

I tried the code below. It works for only a single date in the "Where" clause.

This part is in the cube script:

SCOPE 
  [Post Date].[Date Hierarchy].MEMBERS;
  [Date Calculations].[Calculation].[Rolling 24 Months] = 
    Aggregate
    (
      {
          [Post Date].[Date Hierarchy].CurrentMember.Lag(23)
        : 
          [Post Date].[Date Hierarchy].CurrentMember
      }
     ,[Date Calculations].[Calculation].&[Actuals]
    );
END SCOPE;

MDX below

WITH 
  MEMBER [Measures].NewHires AS 
    (
      [New Hire Types].[New Hire Type ID].&[New Hire]
     ,[Measures].[Employee Events Drill-Through]
    ) 
  MEMBER [Termination Reasons].[Reason Code].Excluded AS 
    Aggregate
    (
      {
        [Termination Reasons].[Reason Code].&[T-RETIRED]
       ,[Termination Reasons].[Reason Code].&[T-NOSTART]
      }
    ) 
  MEMBER [Measures].NewHiresExcluded AS 
    (
      [Termination Reasons].[Reason Code].Excluded
     ,[Measures].[Employee Events Drill-Through]
    ) 
  SET CurrentEmployees AS 
    Filter
    (
      [Employees].[Employee ID].Children
     ,
      [Measures].NewHires >= 1 AND [Measures].NewHiresExcluded = 0
    ) 
  MEMBER [Employees].[Employee ID].[Current2Year] AS 
    Aggregate
    (
      CurrentEmployees
     ,[Employees].[Employee ID]
    ) 
  MEMBER Measures.CurrentEmployees AS 
    --This is working for the month
    (
      [Employees].[Employee ID].[Current2Year]
     ,[Measures].[Employee Events Drill-Through]
     ,[Date Calculations].[Calculation].&[Actuals]
      --[HR Organizational Units].[Entity].[All],
     ,[Event Types].[Event Type].&[1]
    ) 
  MEMBER Measures.[2ndYearRetention] AS 
    Divide
    (
      Measures.CurrentEmployees
     ,[Measures].NewHires
     ,0
    ) 
   ,FORMAT_STRING = 'Percent' 
SELECT 
  {
    Measures.CurrentEmployees
   ,[Measures].NewHires
   ,Measures.[2ndYearRetention]
  } ON COLUMNS
 ,[HR Organizational Units].[Entity].Children ON ROWS
FROM [Employee Events]
WHERE 
  (
    [Date Calculations].[Calculation].&[Rolling 24 Months]
   ,[Post Date].[Date Hierarchy].[FY 2023-Mar]
  );

If you move it to ROWS does it function against multiple months? — whytheq, Jun 09 '23 at 12:38

score 0 · Answer 1 · answered Jun 21 '23 at 12:04

In this section of your cube script you have specified CURRENTMEMBER

SCOPE 
  [Post Date].[Date Hierarchy].MEMBERS;
  [Date Calculations].[Calculation].[Rolling 24 Months] = 
    Aggregate
    (
      {
          [Post Date].[Date Hierarchy].CurrentMember.Lag(23)
        : 
          [Post Date].[Date Hierarchy].CurrentMember
      }
     ,[Date Calculations].[Calculation].&[Actuals]
    );
END SCOPE;

This means that wherever you use Rolling 24 Months there needs to be a singular current member for the cell where the calculation is happening.

If you put a set of dates in the WHERE clause then there is no single current member, hence the exception.

If the set is moved to the ROWS clause then it should be happy, as each cell in the output has a single month context for the currentmember function:

WITH
...
...
SELECT 
  {
    Measures.CurrentEmployees
   ,[Measures].NewHires
   ,Measures.[2ndYearRetention]
  } ON COLUMNS
 ,[HR Organizational Units].[Entity].Children 
  *
  {
    [Post Date].[Date Hierarchy].[FY 2023-Mar],
    [Post Date].[Date Hierarchy].[FY 2023-Feb]
   }
ON ROWS
FROM [Employee Events]
WHERE 
  (
    [Date Calculations].[Calculation].&[Rolling 24 Months]
  );

I need to calculate 2nd year retention for Employees

I tried the code below. It works for only a single date in the "Where" clause.

1 Answers1