Rolling sum with null values

[hvdbunte]

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

[ujjwalraja]

Hi,

Take dimension as id and month.

Expression 1 = minutes

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

You will get your desired output.

[hvdbunte]

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!

[agigliotti]

let's try using the below expression:

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

I hope it helps.

[ujjwalraja · Accepted solution]

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]

Hi Ujjwal,

Thank you for the example. It works great!

Hendri

[Amal]

hi, any idea to not use the script and do it in the measure ? thanks.