Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

Hi,

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.

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

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!

1 Solution

Accepted Solutions
Not applicable

Re: Rank() within a specific dimension when you have multiple ones

After some additional playing around more with the Aggr function, I found the solution:

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


Thank you again Sunny, without your lead I would have not solved it.

6 Replies

Re: Rank() within a specific dimension when you have multiple ones

Try this:

Aggr(Rank(Score), [Unique ID], Category)

Re: Rank() within a specific dimension when you have multiple ones

My bad, the expression should be this:

Aggr(Rank(Score),Category, [Unique ID])

Capture.PNG

Not applicable

Re: Rank() within a specific dimension when you have multiple ones

After some additional playing around more with the Aggr function, I found the solution:

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


Thank you again Sunny, without your lead I would have not solved it.

Re: Rank() within a specific dimension when you have multiple ones

Awesome

I am glad you found what you were looking for. Please close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

masalepp
New Contributor

Re: Rank() within a specific dimension when you have multiple ones

Hi, I have similar type of problem, and to my understanding this function might solve that also.

I have buttons where user can choose the dimension and measure on the graphs. On this one graph, I have also time (months) as an dimension, in addition to what user chooses. My problem is that some of these dimensions have too many unique values, so the line graph gets extremely messy. I would like to show for example top 5 for whatever the user chooses for the dimension. The problem is that the top 5 is not calculated on the measure of that graph but, another measure.

My dimension has the following syntax:  pick($(VTable),Dim1,Dim2,Dim3)

and the measure of the graph:

if(VVolumeDummy = 1, sum({<DEAL={'Won'}>} Turnover) /  sum({<DEAL={'Won','Lost'}>} Turnover),

count({<DEAL={'Won'}>} ID) /  count({<DEAL={'Won','Lost'}>} ID)

and the measure by which I would like to show the top 5:

if(VVolumeDummy = 1, sum({<DEAL={'Won'}>} Turnover),count({<DEAL={'Won'}>} ID)

Is it possible to do a calculated dimension somehow with that similar kind of syntax/logic?

Br

Matti

Re: Rank() within a specific dimension when you have multiple ones

It might be easier if you are able to share a sample and let us know the expected output

Community Browser