Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hvdbunte
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?


Thanks for your help

1 Solution

Accepted Solutions
ujjwalraja
New Contributor III

Re: Rolling sum with null values

Use this script in the backend

temp:

LOAD *

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:

Load

*,

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

resident temp

Order by id;

Drop Table temp;

5 Replies
ujjwalraja
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.

Capture.PNG

hvdbunte
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:

screenshot.JPG

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!

agigliotti
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.

ujjwalraja
New Contributor III

Re: Rolling sum with null values

Use this script in the backend

temp:

LOAD *

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:

Load

*,

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

resident temp

Order by id;

Drop Table temp;

hvdbunte
New Contributor II

Re: Rolling sum with null values

Hi Ujjwal,

Thank you for the example. It works great!

Hendri

Community Browser