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

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

Thu, 06 Oct 2016 12:25:55 GMT

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!

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

sunny_talwar — Thu, 06 Oct 2016 17:23:53 GMT

Try this:

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

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

sunny_talwar — Thu, 06 Oct 2016 17:27:29 GMT

My bad, the expression should be this:

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

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

Fri, 07 Oct 2016 08:27:41 GMT

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

sunny_talwar — Fri, 07 Oct 2016 15:59:25 GMT

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

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

Anonymous — Fri, 04 May 2018 11:58:26 GMT

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?

Matti

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

sunny_talwar — Fri, 04 May 2018 12:04:02 GMT

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