Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

zagzebski
Contributor

Rangesum Above not working

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.

MonthYearNumberAmountAmount (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.

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rangesum Above not working

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

5 Replies
MVP
MVP

Re: Rangesum Above not working

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

zagzebski
Contributor

Re: Rangesum Above not working

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

Not applicable

Re: Rangesum Above not working

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

Not applicable

Re: Rangesum Above not working

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.

Highlighted
MVP
MVP

Re: Rangesum Above not working

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