Using Before to create a running total

Fri, 06 Dec 2013 12:38:03 GMT

I'm having some problems with the Before function. Here is a simplified version of what I'm trying to achieve:

	Year	2012	2012	2012	2013	2013	2013
	Month	October	November	December	January	Feburary	March
A		2	4	4	3	2	4
B		5	6	4	2	2	3
C		8	2	3	6	2	2
Running Total		15	27 (12 + 15)	38 (27 + 11)	49 (38 + 11)	55 (49 + 6)	64 (55 + 9)

This is my expression:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo() ) )

But this is what I am getting:

	Year	2012	2012	2012	2013	2013	2013
	Month	October	November	December	January	Feburary	March
A		2	4	4	3	2	4
B		5	6	4	2	2	3
C		8	2	3	6	2	2
Running Total		15	27 (12 + 15)	38 (27 + 11)	22 (11 + 11)	28 (22 + 6)	37 (28 + 9)

The problem being that in January instead of Before using the Running Total for December it uses the Total for December.

I know this is because "If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom."

But what would be the expression be to calculate the Running Total as I would like?

Many thanks for any help,

Matt

Re: Using Before to create a running total

swuehl — Fri, 06 Dec 2013 12:47:17 GMT

Try adding the TOTAL qualifier to your ColumnNo() function:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )

Re: Using Before to create a running total

Fri, 06 Dec 2013 14:04:38 GMT

So that's what the /total/ in the schema was all about!

Thanks swuehl.

topic Re: Using Before to create a running total in QlikView

Using Before to create a running total

Re: Using Before to create a running total

Re: Using Before to create a running total