Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mattias-thalen
Contributor II
Contributor II

Sum of next N non null values

Hi,

I have a table chart like this:

mattiasthalen_0-1651690916538.png

I'm trying to get the sum of the next 4 non null values (including current value). So I want to start by looking at the current row, and if it's not null gather it. Continue to next row and do the same. When 4 non null values have been collected, I want to sum them. And it needs to happen in the UI/Chart, not in the script.

I've only been able to use Below, but then I need to set a larger window and risk summing more than the next 4 values. If I set it as RangeSum(Below(Column(1), 0 , 4)) then I risk summing fewer values than intended.

I tried using FirstSortedValue and put the null values at the bottom of the sorting, but I think it works by looking at the internal table and not the chart.

Labels (1)
2 Replies
Lisa_P
Employee
Employee

If you exclude the nulls from your chart (in Date column use Limitation > Exact value >0)
and extend range to 4, you get your result ..

Lisa_P_0-1651717252444.png

 

mattias-thalen
Contributor II
Contributor II
Author

I'm sorry for being unclear. This is not an option, all rows must exist.

The solution I have so far is this:

 

Let _var_PBC_MinSignalWindow    = 8;
Let _var_PBC_MaxSignalWindow    = $(_var_PBC_MinSignalWindow)*2;

For i = 0 to $(_var_PBC_MaxSignalWindow)

    Let _var_PBC_SignalWindow       = $(_var_PBC_MinSignalWindow) + $(i);
    Let _var_PBC_SignalBelow        = 'Below(Column(1), 0, $(_var_PBC_SignalWindow))';
    Let _var_PBC_SignalBelowCheck   = 'RangeNumericCount($(_var_PBC_SignalBelow)) = $(_var_PBC_MinSignalWindow)';
    Let _var_PBC_RangeMin_Formula   = 'If($(_var_PBC_SignalBelowCheck), RangeMin($(_var_PBC_SignalBelow)))';
    Let _var_PBC_RangeMax_Formula   = 'If($(_var_PBC_SignalBelowCheck), RangeMax($(_var_PBC_SignalBelow)))';

    Switch i
        Case 0
            Let _var_PBC_RangeMin   = '$(_var_PBC_RangeMin_Formula)';
            Let _var_PBC_RangeMax   = '$(_var_PBC_RangeMax_Formula)';
        
        Default
            Let _var_PBC_RangeMin   = '$(_var_PBC_RangeMin), $(_var_PBC_RangeMin_Formula)';
            Let _var_PBC_RangeMax   = '$(_var_PBC_RangeMax), $(_var_PBC_RangeMax_Formula)';

    End Switch

    Let _var_PBC_SignalWindow       = Null();
    Let _var_PBC_SignalBelow        = Null();
    Let _var_PBC_SignalBelowCheck   = Null();
    Let _var_PBC_RangeMin_Formula   = Null();
    Let _var_PBC_RangeMax_Formula   = Null();

Next i

Let i = Null();

Set _var_PBC_RangeMin = Alt($(_var_PBC_RangeMin));
Set _var_PBC_RangeMax = Alt($(_var_PBC_RangeMax));

 

 

So basically I do a RangeNumericCount for the range N, if that matches N, then proceed.

Else, check N+1, N+2, N+3... N*2