Search code examples
rankingrankqlikviewqliksense

Qlik sense - Rank() within a specific dimension when you have multiple ones

I am new to Qlik and trying to solve the following issue.

I have a table with two dimensions, one with the entry's unique ID, and one with a category, as in the example below.

Table example

My goal is to create a new column with a ranking of 'Score' - my measure - per category:

Table with desired output

If I use the expression

Rank(Score)

I get a column of ones, as the command takes the most granular dimension (Unique ID) as the default one. If I use

Rank(TOTAL Score)

It obviously returns a ranking regardless of all the dimensions. By reading the documentation and similar questions asked by other users I reckon that it should be possible to specify which dimension to use for TOTAL, with the following syntax:

Rank(TOTAL <Category> Score)

Yet, the formula returns an error and only null column values. I've tried different syntax, use of brackets but I still cannot grasp what I am doing wrong.

Please note that I cannot create the ranking column when loading the data.

I would immensely appreciate if someone were so kind to help on this!

Solution

  • Try with 

    =aggr(rank(sum(Score)), Category, UniqueID)