Rank Calculation with maximum amount of rows

[Slindheim]

The Problem is as follows:

We want to do a ranking of sales in EUR and in pieces but we only want to see the first 25 places. Therefore we created a Variable which limits the line of rows to 25. This works perfectly fine. Our rank calculation is like this:

aggr(rank(SUM({< flag_THISWEEK = {1} , AsOfWeek_final = {"$(=vKalenderAktuelleWoche)"} >} VERKAUF_WERT),3,1),ARTIKEL_NR)

The problem is: we display rank EUR next to rank pieces.
There are several products which were sold only once a week, but they are quite expensive. So they appear on top of the list in the EUR ranking, but they have a very low rank in terms of sold pieces.
You will see it in the screenshot.

The issue now is that Qlik calculates the rank on a global level of the data and we want to calculate the rank only on the specific level of the maximum amount of rows.

Our question is: can we calculate the rank only for the maximum amount of lines? In this example: is it possible that the rank calculation only ranks from 1 to 25? So, the third rank of Sales EUR should be the 25th place in pieces.

[morgankejerhag]

Are you saying you wish to calculate the EUR rank 1-25 within the subset of items that have ranked 1-25 in pcs? I haven't done this before, but I am thinking you could maybe find the 25 ranking items in pcs and pass that in P() function in set analysis.

[Slindheim]

It is kind of hard to explain.

Lets say we have an article stock of 1000 different articles. We want to create a ranking in a table which shows the rank EUR (sales) in the first column and the rank pieces sold in the second column. After these two columns we have the columns brand, subbrand, Sale EUR, Sale PCS and so on.

We created a varibale which limits the lines which are displayed. In this case, lets say, we limit the lines to 25.

So, we want to create a hitlist of the 25 best sold products per week which are ordered by the rank of EUR sales.

Now there is the problem that the ranking in EUR sales orders the whole table. So, if one product is very expensive, but is sold only once, it is ranked on the - lets say - 1st place in sales. But in terms of pieces sold it is ranked in 900th position.

Our intention is that we have a ranking which is limited to the subset of products which are ordered/selected by the ranking in EUR sales.
So, we only want the places to be given in the maximum amount of lines which are limited by our variable.

In other terms: we want qlik to think that there are only 25 products which can be ranked overall.

Even though the ranking of the product in pieces sold is 900, we want to display this product with the ranking 25 in pieces, because it should be the lowest position in the ranking of 25 products.

I hope you can understand our problem. As I said: it is very hard to explain and kind of complex.

The P() function is not functionable in this case, unfortunately.

[morgankejerhag]

You could do that calculation with aggr(). I have a simple example with [ProductID], [Amount] where the same ProductID can be repeated. This will give me a frequency rank for the ProductIDs with a value rank <= 10

aggr(if(rank(sum(Amount))<=10,rank(count(Amount))),ProductID)

[Slindheim]

This is an excerpt from our Ranking.

The code which you provided doesn't work in this example. I tried to adapt your code to ours. But it didn't work, unfortunately.

The ranking/table is ordered by sum(sales) (here: VK€ (brutto)).

The limit is set to 100 in rank EUR. I guess by seeing the screenshot one can easily understand our problem.

In this case the rank EUR in 6th position should have ~100th position in terms of pieces sold (VK Stk.). Instead the ranking is 916.

Obviously Qlik uses the whole stock of sold products instead of only the top 100 to calculate the rank of pieces sold.

The perfect solution would be a calculation of the ranking in pieces sold only on the basis of the ranking up to the 100th position of the EUR rank.

I guess we could solve this beforehand in the data load, in the editor. But I hope there is a more elegant way to solve our problem.