Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
piotr-bratek
Contributor III
Contributor III

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!

ex-Training Manager at Qlik Central-Eastern Europe
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
Contributor III
Contributor III
Author

@Henric_Cronström ,  @swuehl , @sunny_talwar   any suggestions ?  🙂

ex-Training Manager at Qlik Central-Eastern Europe
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

piotr-bratek
Contributor III
Contributor III
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

ex-Training Manager at Qlik Central-Eastern Europe
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?