8 Replies Latest reply: Jun 12, 2018 11:45 AM by LUIGI ZANETTI RSS

    Missing values AND aggregations


      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:




          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