7 Replies Latest reply on Nov 30, 2018 2:53 AM by Nagendra Babu

    Max on a Running_Sum

    Hanye Lin

      I am working on a project which requires max on running sum, basic idea is:

       

      I have a dataset as follows:

       

       

      DateValue
      2014.1.115
      2014.1.29
      2014.1.35
      ...
      2015.1.114
      2015.1.216
      2015.1.38
      ...
      2016.1.113
      2016.1.212
      2016.1.317
      ...
      ...
      ...

                                                  

       

       

      Step 1: obtain the value in excess of 10 with bulidin look back period (for example if I only wanted to see the data from most recent x years)

      Step 2: obtain the running sum of those values for each year

      Step 3: For each days, pick up the max runningsum

       

      Ideal Output will be:

      Fixed DayValue
      1904.1.15
      1904.1.210
      1904.1.312
      ...

       

       

      Ideal output is based on:

      DateFixed DayValue in excess of 10Running Sum
      2014.1.11904.1.155
      2014.1.21904.1.200
      2014.1.31904.1.300
      ...
      2015.1.11904.1.144
      2015.1.21904.1.2610
      2015.1.31904.1.3010
      ...
      2016.1.11904.1.133
      2016.1.21904.1.225
      2016.1.31904.1.3712
      ......
      ......
      ......