Multiplication of rows with the cumulative total by months

[lylererger]

Hi!

How to Calculate inside the load script accumulation by moths with multiplication of rows?

The Final Report Shoult look like this

               feb-2014, mar-2014, apr-2014, maj-2014, june-2014

jan-2014  -               7               7               7               7

feb-2014  1               5               7             8               8

mar-2014  -               -               1               1               4

apr-2014    -               -               1               5               8

Any Ideas?

Please help.

[petter]

How is your source table - exactly like your screenshot?

[lylererger]

No.

I'm just attached source table.

There are two periods.

KPI - is just the Count of ContractID.

[petter]

The column headers are unreadable to me ... Could I have the relevant one's in English please?

[lylererger]

Yes. here is attached source file.

[petter · Accepted solution]

In a pivot table which is most practical here you will have to resort to the Before() Inter Record Chart Function. With Before you can retrieve a range of numbers before the current column and you can specify how many.

So  Before(  Sum( [Amount of Contracts] ) , 0 , ColumnNo() )  will give you all numbers to the left including the current column. 0 means current column and ColumnNo() is the current Column Number which is exactly the number of columns that we need to get.

Finally we have to use the RangeSum() to sum the range of numbers that Before() will return...

So the total expression look like this: RangeSum( Before( Sum( [Amount of Contracts] ) , 0 , ColumnNo() ) )

[lylererger]

Thank's a lot Petter. It's so helpful.