3 Replies Latest reply: Jul 11, 2018 9:41 AM by Sunny Talwar RSS

    Aggr Using 2 cumulative functions

    Elijah Abel

      I am trying to complete the visualization calculation for Max Drawdown: https://www.investopedia.com/terms/m/maximum-drawdown-mdd.asp

       

      Max Drawdown must be calculated dynamically when a user selects a date range, so I cannot calculate this in my load script.

       

      Definitions:

      Drawdown = Cumulative PNL - Maximum of Daily Cumulative PNL in a selected date range

      Max Drawdown would be the negative Drawdown value with the greatest magnitude of this across a selected time series.

       

      Assume my Data is like the below:

      DateDepartmentPNL
      2018/06/03A50
      2018/06/04A60
      2018/06/05A10
      2018/06/06A-50
      2018/06/07A20

       

      I have already successfully come up with a formula to calculate the Maximum of Daily Cumulative PNL in a selected date range:

      Max(Aggr(RangeSum(Above(Sum(PNL), 0, RowNo())), Department, (Date, (TEXT, Ascending))))


      For the above data, we would get the max cumulative of 120, and ideally the drawdown calculations below:

       

      DateDepartmentPNLCumulative PNLMax(Cumulative)DrawdownMax Drawdown
      2018/06/03A50505000
      2018/06/04A6011011000
      2018/06/05A1012012000
      2018/06/06A-5070120-50-50
      2018/06/07A2090120-30-50


      How can I calculate Max Drawdown in a field if the table I am trying to put it in should look like the below with the date range of 2018/06/03 - 2018/06/07:

      DepartmentPNL
      Max(CumulativeDrawdownMax Drawdown
      A90120-30-50
      B
      C
      ...

       

      I have tried the following, but it does not give any numbers, only '-' in each cell of the column:

      Min(Aggr(RangeSum(Above(Sum(DV01PNL), 0, RowNo())) - Max(RangeSum(Above(Sum(DV01PNL), 0, RowNo()))), DEPARTMENT, (REPORT_DATE, (TEXT, Ascending))))