Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
piotr-bratek
Creator
Creator

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

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.

recurrent-expected-value-qlik.jpg

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!

The most experienced Qlik Trainer in Central Europe
ex-Training Manager at Qlik CEE
Labels (1)
1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

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

2021-08-17 23_33_41-QlikView x64 Personal Edition - [E__Users_Stefan_Downloads_test_dynamic_rank.qvw.png

View solution in original post

4 Replies
piotr-bratek
Creator
Creator
Author

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

The most experienced Qlik Trainer in Central Europe
ex-Training Manager at Qlik CEE
swuehl
MVP
MVP

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

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

2021-08-17 23_33_41-QlikView x64 Personal Edition - [E__Users_Stefan_Downloads_test_dynamic_rank.qvw.png

piotr-bratek
Creator
Creator
Author

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?

winloss.jpg

The most experienced Qlik Trainer in Central Europe
ex-Training Manager at Qlik CEE
swuehl
MVP
MVP

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?