4 Replies Latest reply on Jul 25, 2014 12:49 PM by K J

    Group based on two dimensions: start time & end time, and Plot

    K J

      The csv file looks like this:

       

      DateStrategyStartTimeIntendedEndTimeNotionalActualEndTime
      2014.07.01A9:30:0016:00:0080084.6215:00:00
      2014.07.01A11:35:0015:00:0017843.9914:30:00
      2014.07.01B12:45:0014:00:0060092.8713:00:00
      2014.07.01B15:55:0016:00:0080168.2715:59:30
      2014.07.02A9:30:0016:00:0060149.3615:00:00
      2014.07.02A11:35:0015:00:0059607.814:30:00
      2014.07.02B12:45:0013:45:0078311.7413:30:00
      2014.07.02B15:55:0016:00:002758.65615:56:00
      2014.07.03A9:40:0016:00:0060149.3615:00:00
      2014.07.03B11:55:0015:00:0059607.814:00:00
      2014.07.03B12:45:0013:45:0078311.7413:00:00
      2014.07.03B15:55:0016:00:002758.65615:56:00

       

      Goal:

      Part 1:

       

      I want to group these events by "StartTime" and "IntendedEndTime", to sum the "Notion" then divide it by total Notional to get the percentage,  and also take the median "ActualEndTime" as a new column. "Date" and "Strategy" will be used as filters. The result will look like this:

       

      StartTimeIntendedEndTimeSumNotionalMedianActualEndTime
      9:30:0016:00:00140233.982715:00:00


      ==================================== EDIT ========================================

       

      I posted a new workbook in which I created a hierarchy for StartTime and EndTime, so I can get the sum notional now. I can also get

      sum/median ActualEndTime part if I substract ActualEndTime by #00:00:00# to get an integer, but not median (requires extract, what does it mean??).

       

      I also don't know how to convert integer back to the hh:mm:ss format..

      ==================================================================================

      Part 2:

       

      Then I want to plot the new dataset like this:

      The blue line is from StartTime to IntendedEndTime. The red line is from StartTime to ActualEndTime.

       

      question_tech_support_july21.png

       

       

      I have attached both workbook and the csv file. Please help.

       

      Thank you very much!