9 Replies Latest reply: Jan 24, 2017 2:48 PM by Sunny Talwar RSS

    Wrong accumulative sum by independent field

    Андрей Шепель

      Colleagues, good day!

      Some questions about calculation in expression, which use logic of accumulative sum.

       

      I have one simple table with such fields:

        

      DateQty
      20.12.2016 13:24:431,000
      22.12.2016 14:40:108,000
      22.12.2016 15:48:362,000
      23.12.2016 12:45:401,000

      Then i load second table:

       

       

      Date2
      01.12.2016
      02.12.2016
      03.12.2016
      04.12.2016
      05.12.2016
      06.12.2016
      07.12.2016
      08.12.2016
      09.12.2016
      10.12.2016
      11.12.2016
      12.12.2016
      13.12.2016
      14.12.2016
      15.12.2016
      16.12.2016
      17.12.2016
      18.12.2016
      19.12.2016
      20.12.2016
      21.12.2016
      22.12.2016
      23.12.2016
      24.12.2016
      25.12.2016
      26.12.2016
      27.12.2016
      28.12.2016
      29.12.2016
      30.12.2016
      31.12.2016

       

      and calculate in pivot sum of Qty by Date2 field with such logic: each value for next date must add previous sum of Qty:

      my expr:

       

      sum(if(Date2>=Date, Qty))

       

      in result i get wrong values:

       

        

      Date2Result
      21.12.20161
      22.12.20161
      23.12.201611
      24.12.201612
      25.12.201612
      26.12.201612
      27.12.201612
      28.12.201612
      29.12.201612
      30.12.201612
      31.12.201612

      Must be:

       

        

      Date2Result
      20.12.20161
      21.12.20161
      22.12.201611
      23.12.201612
      24.12.201612
      25.12.201612
      26.12.201612
      27.12.201612
      28.12.201612
      29.12.201612
      30.12.201612
      31.12.201612

       

       

      Can anyone thinks about my mistake in expr?

      Thanks.