Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chaitanya_samsu
New Contributor III

add up daily values in a column and get cumulative sum column

Hi Team,


I have a table like this with only daily number. I want  insert a 'cum' column which has daily values added up till date.

When I load while loading file I am getting 'above' is invalid error.

DatedailynoCum
07-01-20171010
07-02-20172030
07-03-20174070
07-04-201730100
07-05-201720120
07-06-201730150
07-07-201720170
07-08-201710180
07-09-201740220
07-10-201720240

rangesum(above(dailyno),0,rowno())) did not work for me.

Here is how I tried this.

LOAD

date,

dailyno

FROM [lib://main (qlik_admin)/dailydata.qvd]

(qvd);

I have loaded extra coloumn, it did not work


LOAD

date,

dailyno,

rangesum(above(dailyno),0,rowno()))

FROM [lib://main (qlik_admin)/dailydata.qvd]

(qvd);

The help is appreciated. Thank you.

1 Solution

Accepted Solutions
devarasu07
Honored Contributor II

Re: add up daily values in a column and get cumulative sum column

Hi,

Try like below,

Data:

LOAD

    Date,

    dailyno,

    RangeSum(dailyno, Peek('CumSum')) AS CumSum;

LOAD * INLINE [

    Date, dailyno,

    07-01-2017, 10

    07-02-2017, 20

    07-03-2017, 40

    07-04-2017, 30

    07-05-2017, 20

    07-06-2017, 30

    07-07-2017, 20

    07-08-2017, 10

    07-09-2017, 40

    07-10-2017, 20

];

Capture.JPG

11 Replies
devarasu07
Honored Contributor II

Re: add up daily values in a column and get cumulative sum column

Hi,

Try like below,

Data:

LOAD

    Date,

    dailyno,

    RangeSum(dailyno, Peek('CumSum')) AS CumSum;

LOAD * INLINE [

    Date, dailyno,

    07-01-2017, 10

    07-02-2017, 20

    07-03-2017, 40

    07-04-2017, 30

    07-05-2017, 20

    07-06-2017, 30

    07-07-2017, 20

    07-08-2017, 10

    07-09-2017, 40

    07-10-2017, 20

];

Capture.JPG

vkish16161
Contributor III

Re: add up daily values in a column and get cumulative sum column

Hi,

Above function works in charts only. If you want it there,

Try like this

Rangesum(

Above (

DailyNo, 0, rowno()

)

)

Not applicable

Re: add up daily values in a column and get cumulative sum column

In front end use this expression

rangesum(above(total dailyno,0,RowNo(total)))

chanin7893
New Contributor III

Re: add up daily values in a column and get cumulative sum column

Try This expression

RangeSum(Above (Total Sum(dailyno),0,RowNo(Total)))

Re: add up daily values in a column and get cumulative sum column

Hi,

This seems ok on the script you can check this

LOAD

date,

dailyno,

Rangesum ( Above( dailyno ), 0 , Rowno( ) ) ) as CummDailyNO

FROM [lib://main (qlik_admin)/dailydata.qvd]

(qvd);

Regards

Anand

chaitanya_samsu
New Contributor III

Re: add up daily values in a column and get cumulative sum column

Thanks Devarasu. It worked like Charm!

chaitanya_samsu
New Contributor III

Re: add up daily values in a column and get cumulative sum column

The above function won't be accepted in the data load. It is accepted only in the expression. But thanks for your reply. I appreciate the help.

chaitanya_samsu
New Contributor III

Re: add up daily values in a column and get cumulative sum column

The above function won't be accepted in the data load. It is accepted only in the expression. But thanks for your reply. I appreciate the help.

chaitanya_samsu
New Contributor III

Re: add up daily values in a column and get cumulative sum column

Hey Anand, The above function won't be accepted in the data load. It is accepted only in the expression. But thanks for your reply. I appreciate the help.