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)




      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).