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

# Filling up consecutive marks using Window_sum ( Moving Calculation)

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

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

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

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

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.