RangeSum has nulls in cumulative trend

[Anonymous]

Hello Qlik Community,

I'm trying to create a pivot table with a Year-Month range of the last 12 months in columns and products as rows.  What I want is a cumulative sum across the months in the pivot table and I get that with the following formula:

RangeSum(Before(Sum({<WorkitemStatus={'','Review','Ready','Analysis','In Progress','In Testing','Done'}>}(FactWorkitemHistoryCount)),0,ColumnNo()))

However, because all products do have data for each month I'm getting nulls in months with no data.  What I want to see the the same value from the previous month when there is no change data for the current month.

The data source is a fact table with two rows for each change timestamp.  One to subtract the previous values and another to add the new values.  A sample of the data is attached for explanation.  The entire data model would be necessary to replicate the report.

Thanks for your help.

[avg_knowledge]

Hi,

I have a similar problem when creating a balance sheet in qlik sense.

Using a rangesum formula i have been able to create a cumulative rolling balance sum that sums all balances for the row.

However this only populates when the cell itself has a value - ie if there is a result for sum(Balance) for that cell.

rangesum(before(total sum(Balance),0,12))

This gives the 'Actual' result from the screenshot.

Anyone know of a way to populate null values with the previous populated value? (has to be in a chart expression)

So in the example below, achieve the 'Desired' result from the screenshot.

Thanks

Tom 

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

[Amal]

@sergeyko your solution works when you want to take the previous value. do you have a suggestions when you want the last non-null value ? 

in red is what i have when I don't add you solution . As you can see, I miss some values and I would like to fullfill it with the last non-null value. Your solution (in blue) works only for the first null value.

[sergeyko]

Hey Amal - I think you're right. Adding zeros will work when you don't have consecutive gaps in your data.

What helped me resolve a case similar to yours is a built-in solution for accumulation. Try selecting an Accumulation modifier for your original formula and Qlik will build you a set expression that is capable of dealing with multiple missing values in the data.

See attached for more details.

[Andrei_Faibich]

Built-in Show excluded values option or + Sum({1} 0)  both  will work fine only if you have corresponding data in your data model (it doesn't matter how many consecutive missing value you have). So, if you have missing values you should create dummy record in your data model. The idea is to have all combinations of your dimensions values (Account-MonthYear here) in your data model!

You can compare two data sets

Aggr(Rangesum(Above(Sum(Amount) + Sum({1}0), 0, RowNo())), Customer, MonthYear)

And