5 Replies Latest reply on Jul 13, 2018 1:33 PM by Deepak Rai

    DATEDIFF on LOD Minimum Values

    Andrew Koenig

      I'm trying to track is the amount of time it takes for a [System_Id] to go from the start of Development to the start of when a system is deployed. The database makes a record every time the system has an update, so there are multiple records for every [System_State].  Because of how the data is set up I'm fairly certain I need to use Level of Detail expressions.

       

      Screencap for the lazy:

       

       

      Simply Put, I need:  DateDiff('hour', start of DevStarted, Start of Deployed) for each [System_Id].

       

      so for 2921161 I should get (5/22/2017 3:23:08 PM) -  (2/1/2017 9:36:10 AM)

       

       

      Attempts:

      1. DateDiff MinMax Deployed

       

           if [System_State] = "Deployed" THEN {FIXED [System_Id], [System_State]:(DATEDIFF('hour', Min([System_ChangedDate]), MAX([System_ChangedDate])))} END

           Gives the difference between the highest and lowest entries in a system state (So the time between the most recent update and when a system ID was first deployed).

       

      2. Minimum_System_ChangedDate_by_System_State

       

           {Fixed [System_Id], [System_State]: Min([System_ChangedDate])}

           creates a column showing the earliest entry date for a system state.

       

      I've had a few other ideas, but nothing came as close as those two did. If needed, I've included a column giving each record a rank called System_Rev (resets for each system_Id).