Null handling for RangeSum(Before(Total

[Anonymous]

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()))

[sergeyko]

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.

[sergeyko]

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()))

[pedromsouza]

Thank you so much! It worked!

[Amal]

hello, did it work when you have several gaps ? cause I tried it, and it work only for one gap. thanks.

[saikiranrao]

Hello, same issue for me as well, it is not working for several gaps...

Please let me know if the issue is resolved.

[sergeyko]

Hi there - if your set expression is not very complex, there is one other thing you can try.  Write your expression as a simple aggregation (e.g. simple sum or count that does not go over a rolling period) and then go to the measure settings menu and select Modifier -> Accumulation (pick Range: Full for an all-time rolling window and be sure to check "Show Excluded Values" flag). Qlik will add some fancy set analysis to your expression and it may be able to deal with multiple missing values in your data set.