Recurrent incremental value in a Qlik Sense Table object (alternatively Straight Table in QlikView)

[piotr-bratek]

Hi Guys,

I'm looking for the fastest way to calculate an ExpectedValue according to the algorithm presented below.
IMPORTANT: it must be done on a sheet in the TABLE OBJECT, not in the script as the calculation must work dynamically based on filter selections.

You can use LOAD INLINE given below:

RecurrentExpectedValue:
LOAD * INLINE [
Flag, ExpectedValue
1, 1
1, 2
0, 0
1, 1
1, 2
1, 3
0, 0
1, 1
1, 2
1, 3
1, 4
0, 0
0, 0
1, 1
];

cheers!

[piotr-bratek]

@hic ,  @swuehl , @sunny_talwar   any suggestions ?  🙂

[swuehl · Accepted solution]

Maybe use a the Above() function with a recursive reference to the expression column, something like

=If(Flag=0,0,Rangesum(1, Above("Result")))

[piotr-bratek]

Looks good! I had a similar one but RangeSum enables to avoid 1st row evaluation.

So I have one more question:

I analyze Bundesliga matches looking for win\loss\draw series. The only thing that remain is to display the MAX value from the HowManyInArow column in a KPI. It should be done dynamically as I can filter by either a season or a team. I'm struggling with it as we can't refer to a column name in a specific table. 

I have not found a way using AGGR either, as we have recurrent calculation.

is it even possible?

[swuehl]

Using QlikView, just change the "Total Mode" of the expression (shown in my screenshot) to max of rows.

Besides this, looking at your soccer team results project, I think it should be possible to calculate the series in the script, don't you?