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

            Stefan,

             

            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.

               

              2016-03-01_17-03-18.png

               

              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

              END

               

              Date

              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.