Iterative Calculation for ranking

[Anonymous]

Dear all,

I've got one question on how to manage a specific case (I'm not asking for the entire solution, but I'm struggling with how to build the data model - so the basic steps / ideas are highly appreciated :))

I have a table - let's say with different people liking different kind of fruits:

name	fruit
Stephan	apple
Stephan	Banana
Michael	Orange
Michael	Banana
Stephan	Strawberry

In fact the list is much larger by the way...

What I want to know:

I want to create an iterative Top-List, that means in that example:

Top1: Stephan: 3 kind of fruits

Top2: Michael: 1 kind (because for the next one I only want to count the fruits - not included in Top1

I hope this is understandable...

My question is how to manage this - I played with quite a lot possibilities (variable, set expressions) but I can only get to the Top2 

Additionally I need the calculation within the app, as (e.g. in this example) I want to exclude banana and recalculate my Top-List.

Any ideas how the approach could be?

Kind regards

Thorsten

[avkeep01 · Accepted solution]

Hi Thorsten ,

First step, if I understood it correct, is defining the top 1, which is the name with the most fruit right? 
The next step is getting the fruits from the top 1 and check that for the second one (with the most fruit)? 

So step 1: set Variable vTop1Name;

=AGGR(IF( RANK(COUNT(fruit))=1,name),name)

and step 2 set variable: vTop1Fruit: 

=CHR(34)&CONCAT({$<name={"$(vTop1Name)"}>}fruit,CHR(34)&','&CHR(34))&CHR(34)

Then you can use this expression:

COUNT({$<fruit={$(vTop1Fruit)}>}fruit)

That results in: 

name	fruit
	4
Stephan	3
Michael	1

[avkeep01 · Accepted solution]

Sorry, you want to count the fruits not in the top 1. 

That is the following expression (difference with count included in top 1 in red)

COUNT({$<fruit-={$(vTop1Fruit)}>}fruit)

[Anonymous]

Great! Thank you very much.

As I said, it is a quite big dataset - so I wonder how I could put that in a diagram table.

At the Moment I'm defining several tetboxes to calculate each rank:

E.g. the Top2:

=AGGR(IF( RANK(COUNT(Distinct{$<fruit -={$(vTop1fruit)}>}(fruit)))=1,name),name)

And the number of distinct fruits:

=Max(Aggr( Count(Distinct {$<fruit -={$(vTop1fruit)}>}fruit),name))

That would actually work, but I am wondering how I could Display the result in one table diagram.

I tried it using an If Statement but Qlikview wasn't willing to accept this…

Can you give me a hint?

Kind regards

Thorsten

[avkeep01]

Hi @Anonymous,

Is it that the number 3 also looks at the results of numbers 1 and 2? and that number 4 looks at numbers 1, 2 and 3? 

Or is just the top 1 defining the fruits in this case? 

[Anonymous]

Hi @avkeep01,

yes - and I managed to calculate it in the same way - appending the fruits to the variable (Top3 excluding fruits from Top2 and Top1).

And I also managed to show that in one table (in a quite ugly if-clause - the filed Top is a basic table with the rank 1 till 10):

=IF(Top=1,
Max(Aggr( Count(Distinct fruit),name)),
IF(Top=2,
vTop2nofruits,
IF(Top=3,
vTop3nofruits,
IF(Top=4,
vTop4nofruits,
IF(Top=5,
vTop5nofruits,
IF(Top=6,
vTop6nofruits,
IF(Top=7,
vTop7nofruits,
IF(Top=8,
vTop8nofruits,
IF(Top=9,
vTop9nofruits,
IF(Top=10,
vTop10nofruits
,0)
)))))))))

where the new variable vTop2nofruits:

=Max(Aggr( Count(Distinct {$<fruit -={$(vTop3fruit)}>}fruit,name))

So far, so good.

What I didn't made to work is to make a kind of lookup in the same table.

That means for Top1 Fruit I am showing the name, but I also want to show the city where she/he lives. There is one table having name, fruit, city, phone.

Do you have an idea? Or am I doing it wrong?

Kind regards

Thorsten

PD: I'll mark the thread above as solution, right?

[avkeep01 · Accepted solution]

---

@Anonymous wrote:

And I also managed to show that in one table (in a quite ugly if-clause - the filed Top is a basic table with the rank 1 till 10):

---

You can replace the expression with: 

=PICK(Top,
Max(Aggr( Count(Distinct fruit),name)),
vTop2nofruits,
vTop3nofruits,
vTop4nofruits,
vTop5nofruits,
vTop6nofruits,
vTop7nofruits,
vTop8nofruits,
vTop9nofruits,
vTop10nofruits)

The pick function looks at the number of Top and then search for the calculation. So Top = 1 = Max(Aggr( Count(Distinct fruit),name)). And Top = 2 = vTopNofruits. etc. An if statement calculates all expression, the pick function calculates only the specific part based on the first number. 

---

@Anonymous wrote:

What I didn't made to work is to make a kind of lookup in the same table.

That means for Top1 Fruit I am showing the name, but I also want to show the city where she/he lives. There is one table having name, fruit, city, phone.

Do you have an idea? Or am I doing it wrong?

---

For the lookup you can try: 

ONLY({$<name={"$(vTop1Name)"}>}City)

---

@Anonymous wrote:

PD: I'll mark the thread above as solution, right?

---

If it was the solution for your problem, yes. 

[Anonymous]

Hi @avkeep01,

thank you so much.

One last question:

If there are two persons having the same amount of fruits, than that concept doesn't work and Qlik is show no result.

Is there a workaround to manage this?

Kind regards

Thorsten

[avkeep01 · Accepted solution]

Maybe if you edit the way of sorting. 

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Ranki...

By default the mode is set to share when ranks fall in the same group. Try: 

=AGGR(IF( RANK(COUNT(fruit),4)=1,name),name)

mode

The second argument, mode, can take the following values:

Value Description

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all rows.
2	Average rank on all rows.
3	Highest rank on all rows.
4	Lowest rank on first row, then incremented by one for each row.

There are some examples attached in the help.