locked
mdx DateDiff with current time RRS feed

  • Question

  • Hi All

    How to get datediff of the latesttime in cube  with currenttime ( sysdatetime) . i tried the below query which is not giving exact value can please help to get the datediff within minutes .

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur]

    member

    m2 as datediff("d",[Date].[Year - Month - Date].currentmember.name,StrToMember("[Date].[Date].[" + Format(now(), "MM/DD/YYYY") + "]"))

    select

    {m1,m2} on 0,

    LastSample

    on 1

    from

    [LSM]


    Surendra Thota

    Friday, February 10, 2012 11:02 AM

Answers

  • Hi All

    finally i got the result , so we have to make the Date format YYYY-MM-dd to avoid the mismatch error what we are getting in datediff function .

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur]

    member

    cubeLatestDatetime as Format([Date].[Year - Month - Date].currentmember.membervalue ,"yyyy-MM-dd")+" "+[Time].[Time Hierarchy].currentmember.membervalue

    member

    presentTime as Format(now(), "yyyy-MM-dd HH:mm")

    member

    Timediff as datediff("N",cubeLatestDatetime,presentTime)

    select

    {m1,presentTime,cubeLatestDatetime,Timediff} on 0,LastSample on 1

    from

    [LSM]


    Surendra Thota

    • Marked as answer by Surendra_Thota Monday, February 13, 2012 3:55 PM
    Monday, February 13, 2012 3:55 PM
  • Hi

    Please use Datediff("N", yourlogic)

    here "N" gives minutes where as "d" gives the days as differences

    so in your case,

    member

    m2 as datediff("N",[Date].[Year - Month - Date].currentmember.name,StrToMember("[Date].[Date].[" + Format(now(), "MM/DD/YYYY") + "]"))

    Please let me know for any further details

    Just adding, I wrote a blog post on this topic and might be helpful to you

    http://bipassion.wordpress.com/2011/12/18/mdx-datediff/


    Prav



    • Edited by Prav_SQL Friday, February 10, 2012 12:51 PM
    • Proposed as answer by Ramakrishnan.lh Friday, February 10, 2012 1:33 PM
    • Marked as answer by Surendra_Thota Friday, February 10, 2012 3:06 PM
    Friday, February 10, 2012 12:43 PM

All replies

  • Hi  Surendra,

      Does your date include the current date as one of the members in your time dimension?

    check whether the cube shows today's date as one of the member in your time dimension.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, February 10, 2012 11:45 AM
  • Hi Ram

    Actually i want the  delay in minutes in cube . for that i am getting latestdatetime avialable for that measure and then i have to do datediff on minutes of getdate() . How we can achieve in mdx ?


    Surendra Thota

    Friday, February 10, 2012 11:51 AM
  • Hi

    Please use Datediff("N", yourlogic)

    here "N" gives minutes where as "d" gives the days as differences

    so in your case,

    member

    m2 as datediff("N",[Date].[Year - Month - Date].currentmember.name,StrToMember("[Date].[Date].[" + Format(now(), "MM/DD/YYYY") + "]"))

    Please let me know for any further details

    Just adding, I wrote a blog post on this topic and might be helpful to you

    http://bipassion.wordpress.com/2011/12/18/mdx-datediff/


    Prav



    • Edited by Prav_SQL Friday, February 10, 2012 12:51 PM
    • Proposed as answer by Ramakrishnan.lh Friday, February 10, 2012 1:33 PM
    • Marked as answer by Surendra_Thota Friday, February 10, 2012 3:06 PM
    Friday, February 10, 2012 12:43 PM
  • Hi Prav

    the mdx query is returning error .can you please let me know how calculate using lastsample set .


    Surendra Thota

    Friday, February 10, 2012 1:44 PM
  • Hi

    Here your using as

    [Date].[Year - Month - Date].currentmember.name as one date

    and your using the StrToMember("[Date].[Date].[" + Format(now(), "MM/DD/YYYY") + "]")) another member to get the difference in minutes

    I think, I could expect the error as these two members are in different formats,

    Could you please give some screenshots are the data format

    eg. first member is Jan2012 then the second member is also needs to be Feb2012 format,

    Could you also post the exact error message so it will help me to understand exactly what we are missing.


    Prav

    Friday, February 10, 2012 1:51 PM
  • Hi Prav

    here is the screen shoot


    Surendra Thota

    Friday, February 10, 2012 2:17 PM
  • Hi

    could you see the results as two different members before using the datediff so for testing, we need to create member m2 with system date and another member with your currentmember.

    Here we need to get the results as values in the result set, could you please conform with any info. This is fairly easy task actually.


    Prav

    Friday, February 10, 2012 2:26 PM
  • hi Prav

    here is the result


    Surendra Thota

    Friday, February 10, 2012 2:58 PM
  • Hi Prav

    Final it worked. thanks for your help

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur]

    member

    presentTime as Format(now(), "MM/dd/yyyy HH:mm")

    member

    cubeLatestDatetime as [Date].[Year - Month - Date].currentmember.name+" "+[Time].[Time Hierarchy].currentmember.name

    member

    Timediff as datediff("N",cubeLatestDatetime,presentTime)

    select

    {m1,presentTime,cubeLatestDatetime,Timediff} on 0,LastSample on 1

    from

    [LSM]


    Surendra Thota

    Friday, February 10, 2012 3:05 PM
  • Hi

    Greak to know that you got solution, However it is best practice to use the SOLVE_ORDER on interdependecy of calculated members in MDX

    Please refer to this,

    http://msdn.microsoft.com/en-us/library/ms145539.aspx

    I think, it will help you.


    Prav

    Friday, February 10, 2012 4:51 PM
  • hi Prav

    the same query which worked on friday is failing now . it is giving the fallowing error

    VALUE #Error Execution of the managed stored procedure Datediff failed with
    the following error: Exception
    has been thrown by the target of an invocation.Argument 'Date1' cannot be
    converted to type 'Date'..

    what is the reason for this ? i hope this is due to formating , today its 13Th of feb may the server is not accepting this format . how to resolve this issue .


    Surendra Thota

    Monday, February 13, 2012 10:23 AM
  • Hi

    Have you used SOLVE_ORDER steps in calculated members. Please try it and let me know the status. It should it fail as the date chagnes.

    Could you also please check the system date, in case any chagnes.

    Please let me know, we will work on issue


    Prav

    Monday, February 13, 2012 10:55 AM
  • Hi Prav

    i have used the Solve_Order also .but it didnt work .


    Surendra Thota

    Monday, February 13, 2012 11:11 AM
  • hi Prav

    i am attaching the previously worked screenshot


    Surendra Thota

    Monday, February 13, 2012 11:13 AM
  • Hi

    I don't see any Date1 in the script, here we are getting error on Date1 field. could you give the script please.

    Here again please take of NON EMPTY at first member, as we need to take all possible dates and times here, I think off it.

    I will do best of my knowledge here


    Prav

    Monday, February 13, 2012 11:23 AM
  • Hi Prav

    it is a generic error message .Date1 means the first date arrugment presenttime member

    here is the script .

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur], SOLVE_ORDER = 1

    member

    presentTime as Format(now(), "MM/dd/yyyy HH:mm"), SOLVE_ORDER = 2

    member

    cubeLatestDatetime as [Date].[Year - Month - Date].currentmember.name+" "+[Time].[Time Hierarchy].currentmember.name , SOLVE_ORDER = 3

    member

    Timediff as datediff("N",cubeLatestDatetime,presentTime)

    select

    {m1,presentTime,cubeLatestDatetime,Timediff} on 0,LastSample on 1

    from

    [LSM]


    Surendra Thota

    Monday, February 13, 2012 11:32 AM
  • Hi Prav

    For testing i took the yesterday date and its giving result correctly . so the problem is with date format only when it crossing 12 it is considering as month so throwing error . the below query when i tested for previous date worked well .

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur]

    member

    presentTime as Format(now()-1, "MM/dd/yyyy HH:mm")

    member

    cubeLatestDatetime as [Date].[Year - Month - Date].currentmember.prevmember.name+" "+[Time].[Time Hierarchy].currentmember.name

    member

    Timediff as datediff("N",cubeLatestDatetime,presentTime)

    select

    {m1,presentTime,cubeLatestDatetime,Timediff} on 0,LastSample on 1

    from

    [LSM]


    Surendra Thota

    Monday, February 13, 2012 1:52 PM
  • Hi All

    finally i got the result , so we have to make the Date format YYYY-MM-dd to avoid the mismatch error what we are getting in datediff function .

    WITH

    SET LastSample AS TAIL(NONEMPTY([Date].[Year - Month - Date].members *[Time].[Time Hierarchy].members))

    member

    m1 as [Measures].[VS LicensePCSCFConcurSessCur]

    member

    cubeLatestDatetime as Format([Date].[Year - Month - Date].currentmember.membervalue ,"yyyy-MM-dd")+" "+[Time].[Time Hierarchy].currentmember.membervalue

    member

    presentTime as Format(now(), "yyyy-MM-dd HH:mm")

    member

    Timediff as datediff("N",cubeLatestDatetime,presentTime)

    select

    {m1,presentTime,cubeLatestDatetime,Timediff} on 0,LastSample on 1

    from

    [LSM]


    Surendra Thota

    • Marked as answer by Surendra_Thota Monday, February 13, 2012 3:55 PM
    Monday, February 13, 2012 3:55 PM
  • Hi

    Sorry, couldn't make time on it. Great to know that you did it, I supposed the issue with formats only. You won my vote to your solution


    Prav


    • Edited by Prav_SQL Tuesday, February 14, 2012 3:14 PM
    Tuesday, February 14, 2012 3:13 PM