1 Reply Latest reply on Aug 10, 2016 3:57 PM by Shinichiro Murakami

    First three values by date, for different date

    Anna Chan

      Hi

       

      I have a data set that has multiple values which have multiple start dates, is it possible get the sum of the first three values? Please let me know.

       

      Thanks,

      Anna

        • 1. Re: First three values by date, for different date
          Shinichiro Murakami

          I'm quite uncertain I understand your request, but anyway here should be some hint.

          If possible, please attach your packaged workbook (***.twbx) for us to study further more.

           

          I created sample data, in this case, we want to sum up 2016/1/1~2016/3/1.

          The answer should be 2229 which is show as Running_sum on 2016/3/1.

           

          At the same time I added [First] calculated field as index.

          [First]

          first()

           

          Now when you try to get the number of 2229 without any dates on column, you can put the formula like this.

           

          [First Three Values]

          Window_sum(if [First]>=-2 then sum([Value]) END)

           

           

          Then the number is shown in worksheet 3.

          with unchecking Show header of Start Date and filtering only 0 for "Firs" as filter.

           

           

          Thanks,

          Shin

           

          9.1 attached.