Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Null handling for RangeSum(Before(Total

Hello,

I am trying to create a cumulative sum and I am using the below formula.  I have NULL values for some months and would like to see the last value available (ie the current cumulative sum and not 0).  The goal is that after I have at least 1 quantity, all of the subsequent months will have a value and not be NULL.  Attached is my result set.  Thank you.

RangeSum(Before(TOTAL Sum([Quantity]), 0, ColumnNo()))

3 Replies
Highlighted
Contributor III
Contributor III

Hi there,

Have you managed to solve this? Facing the same issue and couldn't figure anything better than wrapping the RangeSum in IF that does Above(RangeSum) if the current is 0. This only helps to deal with single gaps in the data, which does not look like a reliable solution.

I'd appreciate any ideas on how to make a cumulative sum work well with consecutive gaps in the data.
Highlighted
Contributor III
Contributor III

I think I figured it out! You will need to fill the gaps in your data with 0s so that RangeSum can crawl through them smoothly. Just add Sum({1} 0) to your original sum expression and it will replace the NULLs with 0s.

Here's an example:

RangeSum(Above(Sum({<...>}YourMeasure) + Sum ({0} 0),0,RowNo()))

Highlighted
Creator
Creator

Thank you so much! It worked!

Qliking since '09