3 Replies Latest reply on Mar 1, 2016 2:08 PM by Adam Crahen

    Filling up consecutive marks using Window_sum ( Moving Calculation)

    Cody Smith

      Hi All



      I need to fill up the months with the value for each customer for their respective contract term.



      For example Customer A should have the value 100 from April to July.



      I tried using Window_sum function but i am not able to use the dimension Contract time within the formula, Can u suggest me an alternative?

        • 1. Re: Filling up consecutive marks
          Stefan Nikolic

          The way I would do it is:


          IF Customer="A" and Month="Apr" then 100

          ElSEIF Customer="A" and Month="May" then 100

          ElSEIF Customer="A" and Month="Jun" then 100

          ElSEIF Customer="A" and Month="Jul" then 100

          ElSEIF Customer="B" and Month="Jan" then 500

          ElSEIF Customer="B" and Month="Feb" then 500

          ElSEIF Customer="B" and Month="Mar" then 500



          And so forth and so forth...


          Let me know if you have any questions

          • 2. Re: Filling up consecutive marks
            Cody Smith



            Thanks for the quick reply.


            The excel i posted was just a snippet for a large data. Say more than 10000 customers. I thought Window_sum would be a appropriate function to use .


            The formula i used was Window_sum(Sum(amount), 0,sum(contracttime)) but it displayed no values and when i used window_sum(sum(amount),0,4) it displayed the proper amounts.

            Since its a large amount of data i want the values to dynamically change according to the contract time length

            • 3. Re: Filling up consecutive marks
              Adam Crahen

              Hi Cody-


              Give this a try.




              First thing I did was pivot your data.  You have a crosstab there and we want a table.

              Quick Start: Pivot Data (from Columns to Rows)


              Then I turned your month string into a Month # and then a Date.


              Month #

              CASE [Month]  

              WHEN "Jan" THEN 1

              WHEN "Feb" THEN 2

              WHEN "Mar" THEN 3

              WHEN "Apr" THEN 4

              WHEN "May" THEN 5

              WHEN "Jun" THEN 6

              WHEN "Jul" THEN 7

              WHEN "Aug" THEN 8

              WHEN "Sep" THEN 9

              WHEN "Oct" THEN 10

              WHEN "Nov" THEN 11

              WHEN "Dec" THEN 12




              DATE(str([Month #])+"/1/"+str(year(today())))


              Then I figured out the MIN month that had a value


              Min Date

              {Fixed [Customer] : MIN(IF NOT ISNULL([Value]) THEN [Date] END)}


              Then I filled the value based on the contract time


              Value Filled

              If [Date] >= [MIN Date] AND [Date] <= DATEADD('month',[Contract time]-1,[MIN Date]) THEN {fixed [Customer] : max([Value])} END


              Last thing I did was right click on Month(date) in the column shelf and format the dates as an abbreviation.


              9.2 workbook attached.