topic Multiplication of rows with the cumulative total by months in QlikView

Multiplication of rows with the cumulative total by months

lylererger — Sun, 03 May 2015 15:46:40 GMT

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.

Re: Multiplication of rows with the cumulative total by months

petter — Sun, 03 May 2015 16:21:52 GMT

How is your source table - exactly like your screenshot?

Re: Multiplication of rows with the cumulative total by months

lylererger — Sun, 03 May 2015 16:27:42 GMT

No.

I'm just attached source table.

There are two periods.

KPI - is just the Count of ContractID.

Re: Multiplication of rows with the cumulative total by months

petter — Sun, 03 May 2015 16:36:29 GMT

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

Re: Multiplication of rows with the cumulative total by months

lylererger — Sun, 03 May 2015 16:48:35 GMT

Yes. here is attached source file.

Re: Multiplication of rows with the cumulative total by months

petter — Sun, 03 May 2015 18:03:37 GMT

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() ) )

Re: Multiplication of rows with the cumulative total by months

lylererger — Sun, 03 May 2015 18:08:36 GMT

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