3 Replies Latest reply: Jan 24, 2018 1:59 PM by Petter Skjolden RSS

    Variable Pacing using rangesum or sum with set

    Jacob Wapinsky

      Hi all,

       

      I am trying to get a function together to get an idea of how I am doing in terms of getting orders

       

      Sample Data

      DateDate MonthDate YearDelivery MonthBusiness Days Remaining in MonthOrder Quantity
      1/23/2018120182710
      1/23/201812018375
      1/23/2018120182716
      1/15/20181201821320
      12/29/20171220172110
      12/15/2017122017285
      12/19/20171220171750
      12/15/20171220171810

       

      etc.

       

      The output I would like to see is

      With 7 days remaining in January --> how many orders do we have (irregardless of month) and where were we at compared to December at the same time?

       

      Final output desired --> using delivery month of date month +1

       

      Date YearDate MonthDelivery MonthBusiness DaysTotal Orders
      201812761
      2017121760

       

      So far I have tried the following equations --> which work only for current month and not for prior month or on year changes (i.e. looking at January in December)

      Sum({<[Delivery Month]={'"$(=vMaxDateMonth+1)"},[Delivery Year]={'$(vMaxDateYear)'},

      Date = {'<=$(=vMaxDate)'}>}OrderQuantity)

       

      I have also tried using rangesum but I have not gotten it to work in any meaningful way.

       

      Thank you

      Jacob