# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Rolling sum with null values

Hi,

I am new to Qlik Sense but I try to do the following:

ID:  Month:   Minutes:   Total minutes:

1     Jan        10             10

1     Feb        15             25

1    March     null           25

2     Jan        20             20

2     Feb        null           20

2    March     null           20

3     Jan        5               5

3     Feb        10           15

3    March     15           30

.....

I tried this formula that I found in another post:

rangeSum(Minutes) AS Cumulative . But this only returns 0 for the null values.

How can I use this rolling sum with a break and nulls?

Tags (2)
1 Solution

Accepted Solutions
New Contributor III

## Re: Rolling sum with null values

Use this script in the backend

temp:

INLINE [

id, month, minutes

1, Jan, 10

1, Feb, 15

1, March,

2, Jan, 20

2, Feb,

2, March,

3, Jan, 5

3, Feb, 10

3, March, 15

];

Table:

*,

if(id=Previous(id),rangesum(peek('TotalMinutes'),minutes),minutes) as TotalMinutes

resident temp

Order by id;

Drop Table temp;

5 Replies
New Contributor III

## Re: Rolling sum with null values

Hi,

Take dimension as id and month.

Expression 1 = minutes

Expression 2 =rangesum(Above(Sum(minutes),0,RowNo()))

You will get your desired output.

New Contributor II

## Re: Rolling sum with null values

Hi,

Thanks for your quick reply. But I simplified the issue a bit, I shoudn't have... But when I add an aditional column I get the following result:

I would like to add a couple of other columns as well. Would that be an issue?  Or can I pre calculate this in my load script and then join my data to my main table?

Thanks again!

Honored Contributor II

## Re: Rolling sum with null values

let's try using the below expression:

rangesum( Above( total Sum(minutes), 0, 12 ) )

I hope it helps.

New Contributor III

## Re: Rolling sum with null values

Use this script in the backend

temp:

INLINE [

id, month, minutes

1, Jan, 10

1, Feb, 15

1, March,

2, Jan, 20

2, Feb,

2, March,

3, Jan, 5

3, Feb, 10

3, March, 15

];

Table:

*,

if(id=Previous(id),rangesum(peek('TotalMinutes'),minutes),minutes) as TotalMinutes

resident temp

Order by id;

Drop Table temp;

New Contributor II

## Re: Rolling sum with null values

Hi Ujjwal,

Thank you for the example. It works great!

Hendri