Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wale_martins
Contributor III
Contributor III

RANK WHEN USING PICK MATCH FUNCTION

Hi there,

I want to add a Rank function in the following expression that I'm using as a dimensión in a straight table:

=PICK(MATCH($Field,'TAG_COMPROMISO_SECTOR','TAG_ANALITICAS','TAG_VETSICS','TAG_MERCHANDISING','TAG_RESPONSABILIDAD_SOCIAL'),

       [TAG_COMPROMISO_SECTOR],[TAG_ANALITICAS],[TAG_VETSICS],[TAG_MERCHANDISING],[TAG_RESPONSABILIDAD_SOCIAL])


I'm using that expression as dimension because I want to join different column values in a unique dimensión and count the number of times that appear each value in my main table.


=COUNT (DISTINCT ID_VISITA)

Untitled picture.png

My problem is that I want to Rank the dimensión expression.... for example, only showing the top 5 count values but without using dimensión limits because in some cases I don't have to take into consideration the top 5 values for example...

Any idea would be appreciated.

Wale

1 Solution

Accepted Solutions
sunny_talwar

May be like this

If(Rank(Count(DISTINCT ID_VISITA)) < 6, Count(DISTINCT ID_VISITA))

View solution in original post

4 Replies
sunny_talwar

May be like this

If(Rank(Count(DISTINCT ID_VISITA)) < 6, Count(DISTINCT ID_VISITA))

wale_martins
Contributor III
Contributor III
Author

Thank you very much sunny! I've been wasting my time trying to do it in my dimensión expression....

Best Regards

Wale

wale_martins
Contributor III
Contributor III
Author

Hi stalwar1‌,

I have another issue with my Rank expression. The problem is that I want to create three columns. 1 column with the top 19 values, another column with the next 19 top values and the last one with the next 19 top values.

Untitled picture.png

Now that I'm using your expression in the expressions tab, I have problems to créate this three columns...

Top 19 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) <=19,COUNT(DISTINCT ID_VISITA))

Next 19 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) >19,COUNT(DISTINCT ID_VISITA))

Next 38 values; =IF(RANK(COUNT(DISTINCT ID_VISITA),4) >38,COUNT(DISTINCT ID_VISITA))

Untitled picture.png

As you can see, the first column is correct, in the second one only appear 18 values and in the third column do not appear values.

Is there a way to handle that?

Thank you in advance!!

Wale

sunny_talwar

May be use an alternative to calculating the rank here:

Continuous Ranking (no missing Rank) | Qlik Community