Rangesum(Peek), how to handle null values

[phiilip]

Hello all,

So in the load script I have this snippet to make a cumulative measure by week

RangeSum(Peek('Measure'), [Savings]) as Measure

However, for some weeks I have no savings. I would like to make it so that if the realized savings is null, then it takes the previous not null value.

How do I make this work?

[marcus_sommer]

The expression does exactly doing an accumulation as you are describing. Therefore I assume that your issue isn't a matter of any expression-logic else related to missing records. If so you will need to add the missing records in beforehand.

[phiilip]

That is true, the records themselves are missing (no week, no savings). So the only way is to somehow add missing records?

[marcus_sommer]

Yes - you need always a dimension-value to show any measure-result against it. It's a general rule and not specific for Qlik.

In nearly all scenarios it's most suitable to populate the missing data within the data-model and not the UI, for example with something like this:

facts: load * from facts;
concatenate(facts) load distinct Week, 0 as Value
from Calendar where not exists(WeekFacts, Week);

and afterwards the accumulation is applied within a resident-load.