locked
SSAS MDX to find date difference RRS feed

  • Question

  • Hi Experts
    I am working On Cube ,I have a Dimension Employee which has 3 attribute
    and values in Attributes are below (Sample Data)

    JoiningDate  Leaving Date  Classification

    2010-01-20   2011-01-20      Co
    2010-02-20                            C1
    2010-03-20                            C2
    2010-04-20   2011-04-20       C3

    In my REal cube if I drag the date I get below format

    [Employee].[Joining Date].&[2001-02-01T00:00:00]
    [Employee].[Leaving Date].&[2006-07-23T00:00:00]

    I have a Date Dimension where I also have a Hierarchy(Date Dimension is used in Filter to get reports ).It has 4 level
    Year->Semester-> quarter->Month

    Below are the attributes from my Original Cube.

    [Date].[FiscalHierarchy].[FYYear]
    [Date].[FiscalHierarchy].[Fiscal Semester]
    [Date].[FiscalHierarchy].[FYQuarter]
    [Date].[FiscalHierarchy].[FYMonth]

    Requirement: I need to create 2 Calculated Measures in Cube

    1st Measures :to find NUmber of days between

    Leaving Date - Joining date
    But if Leaving Date isnull(Means Employee still working in company)
    then 1st day of Year or Semester or Quarter or Month which ever selected from Filter Minus(-) Joining date


    2nd Cal Measures :  Number of working days with Same condition as above  where Classification is not Equal to C0

    Any Help ,as I am not getting any Clue as what MDX to create



    • Edited by MS308 Monday, May 21, 2012 6:08 AM
    Friday, May 18, 2012 1:37 PM

All replies

  • Hi,

    Generally it is much easier to do these calculations outside of the cube and MDX. Do this when you load the data into the starschema. TSQL has the datediff() function that should solve your first problem. My advice is to create these two measures directly in the fact table.

    HTH

    Thomas Ivarsson

    • Proposed as answer by Lola Wang Monday, May 21, 2012 4:45 AM
    Saturday, May 19, 2012 8:59 AM
  • Hi Thomas,

    Thanks for the Reply.
    I have already done in my DataBase all the Required Changes,but that does not solve my complete Problem Because when
    Leaving date is Not Null then
    Leaving date-Joining Date

    but but when

    Leaving Date isnull  then Leaving date would be First day of Selected Value from report Filter(year or Semester or Quarter or MOnth)Minus(-) Joining date

    user can select any Value(Month or Quarter or Semeter or Year) from PPS report Filter,and I need to take First day of that value - Joining Date

    DateDiff would be useful when Leaving date is Not Null but when it is null then I guess about scenario  can not  be Done in Database.
    Any Help?


    • Edited by MS308 Monday, May 21, 2012 6:07 AM
    Monday, May 21, 2012 6:01 AM
  • Hi Mushtaq,

    If you go by the priniples of SCD Type 2 dimension http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

    You should have a column like "Is_Terminated" which would signify whether the employee is still active with the organization or not.

    If the employee is termintaed the the Leaving Date would have a definite leaving date value. If not, the laving date column will have a default value like "31-12-9999"


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, May 21, 2012 6:08 AM
  • Hi Raunak,

    Thanks for The Reply.
    Actually this is what I did initally :)
    But Its a Client requirement that  They want to see Number of day There Employee Has worked who has left the company or  who is still working ,and they want to see this On the Basic of Filter Value Selected  for the Employee who are still working .

    Monday, May 21, 2012 6:31 AM
  • Hi Mushtaq,

    In that case the design proposed by you is one of the right approach. What are the hurdles, if any?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, May 21, 2012 6:51 AM
  • Hi Raunak,
    Thanks for the Reply

    Hurdle is I need to create calculated Measure in my Cube to achieve above Scenario.(PLZ refer my First thread\Post for complete requirement)

    And I am gettig no Idea what MDX should I write for This ?



    • Edited by MS308 Monday, May 21, 2012 7:00 AM
    Monday, May 21, 2012 6:56 AM
  • Hi Mushtaq,

    Iif(employee_terminated =1, datediff(days,join_date, last_date, datediff(days,join_date, NOW()))

    But such an approach is non deterministic, for the number of days will change everyday for employees who are still with the company.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, May 21, 2012 7:29 AM
  • Hi Raunak ,

    Yes The Number of days will change daily.
    Thats why it is Needed to Take 1st day of Value selected from Filter - Joinig date  to get number of working day till that period ,if Leaving date is Null.
    Can't we use any Current Member or Level Function in MDX to achieve That?


    • Edited by MS308 Monday, May 21, 2012 7:41 AM
    Monday, May 21, 2012 7:39 AM
  • Hello Mustaq,

    Your calculations are not related to a fact table. That why you may look if they could be more easely  performed in ETL or SQL.

    You can try to use datediff like in the following:

    WITH 
      MEMBER [Measures].[Start Date] AS 
        [Employee].[Joining Date].CurrentMember.MemberValue 
      MEMBER [Measures].[End Date] AS 
        [Employee].[Leaving Date].CurrentMember.MemberValue 
      MEMBER [Measures].[Number of days] AS 
        IIF
        (
            [Employee].[Leaving Date].CurrentMember.Name
          = NULL
         ,Datediff
          ('d'
           ,[Measures].[Start Date]
           ,
              {NULL : Now()}.Item(0)
            + 1
          )
         ,Datediff
          ('d'
           ,[Measures].[Start Date]
           ,
            [Measures].[End Date] + 1
          )
        ) 
    SELECT 
      {
        [Measures].[Start Date]
       ,[Measures].[End Date]
       ,[Measures].[Number of days]
      } ON 0
     ,[Employee].[Employee].MEMBERS ON 1
    FROM [Your Cube]
    WHERE 
      (
        {
          [Employee].[Joining Date].&[Bgin] : [Employee].[Joining Date].&[Bgin]
        }
       ,[Employee].[Classification].&[Co]
      );

    with 

    {NULL : Now()}.Item(0)

    should take the first 1st day of Year or Semester or Quarter or Month. 

    Erase 

    [Employee].[Classification].&[Co]

    from the WHERE clause as needed

    Philip,

    Monday, May 21, 2012 9:32 AM
  • Hi Philip,

    Thanks for the Reply.
    Let me check this and Get back to you .

    Thanks Though

    Monday, May 21, 2012 9:53 AM
  • Hello,

    seems like in the hurry i wrote a "provisional" expression in the WHERE clause that deserves some explanations:

      [Employee].[Joining Date].&[Bgin] : [Employee].[Joining Date].&[Bgin]
    

    You should replace it by a date range of the [Employee].[Leaving Date] attribute  that represents  the filtered  Year or Semester or Quarter or Month.

    In this scenario, you won't be able to use hierarchies (year, semester, Monrh) of yr time dimension because your query does not relates to facts .

    Philip,

    Monday, May 21, 2012 11:33 AM