7 Replies Latest reply: Apr 26, 2018 11:29 AM by Sunny Talwar RSS

    Missing values AND aggregations

    LUIGI ZANETTI

      Hi all,

       

      workng with rolling sums I ran into an issue.

      Say I want to have the volume of sales 1 year backwards, month by month.

      I used:

       

      RangeSum(

      After(

          total  Sum( {< [Posting.Date.autoCalendar.YearMonth] = >} Quantity)        , 0,12 )

              )


      The problem is that if I have missing values (sales = 0 in one month) this formula gives me the sum over 13 months (because that month is not listed on the table, so 12 steps backwrd is 13 months).


      Does anyone know how to work around this?


      Want I would like is that the sum goes back 1 year even if there are months not listed (e.g. 2/2018 to 3/2017, 1/2018 to 2/2017, 12/2017 to 1/2017)


      Thank you very much