Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
lylererger
Contributor II

Multiplication of rows with the cumulative total by months

Hi!

1.png

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Multiplication of rows with the cumulative total by months

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

2015-05-03 #9.PNG

View solution in original post

6 Replies
MVP
MVP

Re: Multiplication of rows with the cumulative total by months

How is your source table - exactly like your screenshot?

lylererger
Contributor II

Re: Multiplication of rows with the cumulative total by months

No.

I'm just attached source table.

There are two periods.

KPI - is just the Count of ContractID.

MVP
MVP

Re: Multiplication of rows with the cumulative total by months

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

lylererger
Contributor II

Re: Multiplication of rows with the cumulative total by months

Yes. here is attached source file.

MVP
MVP

Re: Multiplication of rows with the cumulative total by months

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

2015-05-03 #9.PNG

View solution in original post

lylererger
Contributor II

Re: Multiplication of rows with the cumulative total by months

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