topic Re: Rangesum Above not working in QlikView

Rangesum Above not working

zagzebski — Sat, 02 Aug 2014 16:38:52 GMT

I cant get range sum (above) to work properly. I am including the table below.

Amount column expression: =sum(Amount)

Amount (Rangesum above) expression: =rangesum(above(sum(Amount),0,12))

Amount Expected column: I added this to show how I would expect the range sum to work (current month plus accumlated total)

Basically I just want to accumulate the amount column but I must be writing the expression incorrectly.

MonthYearNumber	Amount	Amount (Range Sum Above)	Amount Expected
201301	703,619	703,619	703,619
201302	3,058,985	4,466,223	3,762,604
201303	(2,290,557)	5,938,270	1,472,047
201304	235,148	7,645,465	1,707,195
201305	2,404,384	11,757,045	4,111,579
201306	(1,542,086)	14,326,538	2,569,493
201307	1,559,486	18,455,517	4,128,979
201308	(8,063)	22,576,433	4,120,916
201309	368,769	27,066,118	4,489,685
201310	(1,320,005)	30,235,799	3,169,680
201311	881,620	34,287,100	4,051,300
201312	(1,539,602)	36,798,799	2,511,698

Thanks in advance.

Re: Rangesum Above not working

maxgro — Sat, 02 Aug 2014 16:59:01 GMT

rangesum(above(sum(Amount),0,rowno()))

Re: Rangesum Above not working

zagzebski — Sat, 02 Aug 2014 17:06:08 GMT

Thanks it worked! Can you explain the Row() part of this?

Re: Rangesum Above not working

Sat, 02 Aug 2014 17:10:28 GMT

RangeSum(Above(TOTAL Sum(Amount),0,RowNo(TOTAL)))

Re: Rangesum Above not working

Sat, 02 Aug 2014 17:31:45 GMT

RowNo() function will itself return the row no u r on which secondary dimension within the first dimension eg: months within a year.

Whereas the RowNo(TOTAL) will ignore all the dimensions and returns the row no sequentially of a whole chart.

Re: Rangesum Above not working

maxgro — Sat, 02 Aug 2014 17:34:43 GMT

from online help

RowNo([total])

Returns the number of the current row within the current column segment in a table or, in the case of bitmap charts, within the chart's straight table equivalent. The first row is number 1.

If the table is one-dimensional or if the qualifier total is used as argument, the current column segment is always equal to the entire column.

above(sum(Amount),0,rowno()): n values, the current (0) and all preceding (above) rows

rangesum(...........) sum current and all preceding (above) rows