3 Replies Latest reply on Oct 4, 2017 3:28 PM by Kara Follmer

    Historical Data, Total Open Projects at Any One Time

    Carl Slifer

      Howdy Folks,

       

      For the purposes of this I have data With 3 columns

      ProblemID OpenTime ClosedTime

       

       

       

       

      Many of these are open for a few months or so at a time, I already have this in a Gannt Chart for visual purposes, but what I'd like to do is get these where I can view them on a line graph, daily or weekly. I'm still unsure which to use.

       

      I would use the DATETRUNC() Function in order to move all the hours, minutes, and seconds to just the beginning of each day (or week). If it was open at one point in that time period I would count it, would be my reasoning.

       

      I'm looking for hints how my formula would work to make that line graph show at any given interval (day or week), the total number of open projects there were at that time.

       

      I've done this with a parameter, but it only returns one value at any specific time, here's what I did.

       

      IF [TimeParameter] < [OPEN_TIME] OR [TimeParameter2] >[CLOSE_TIME] THEN 0

      ELSEIF [OPEN_TIME] < [TimeParameter] AND [CLOSE_TIME] > [TimeParameter2] THEN 1

      ELSEIF IIF(ISNULL([CLOSE_TIME]), 1, 0) = 1 THEN 1

       

      ELSE 0 END

       

      This literally says if the time chosen by the analyst is less than the row's open time or its greater than the close time to not count it

      If the time is greater than open and less than close to count it

      Or if the project is still not closed to count it.

       

      This works perfectly but it only returns the one value for that specified time, I am unsure how to make this an actual function that can be graphed.

       

      Thank you,

      Carl