Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mpnewbie
Partner - Contributor III
Partner - Contributor III

Dealing with ties in a Rank function

Hi,

Given the following data:

SubjectVisitNameVisitNumber
SubAScreening10
SubAPrep 120
SubAPrep 221
SubATreatment30
SubBScreening10
SubBPrep 120
SubBTreatment30
SubCScreening10
SubCPrep 120
SubCTreatment30
SubCFollow up 140

 

How do I get to the following table:

SubjectVisitNameVisitNumberRank
SubAScreening101
SubAPrep 1202
SubAPrep 2213
SubATreatment304
SubBScreening101
SubBPrep 1202
SubBTreatment304
SubCScreening101
SubCPrep 1202
SubCTreatment304
SubCFollow up 1405

 

I am currently using the following expression:

rank(TOTAL -aggr(DISTINCT VisitNumber, VisitName, Subject), 1, 1)

Which gives me the following:

SubjectVisitNameVisitNumberRank
SubAScreening101
SubAPrep 1204
SubAPrep 2217
SubATreatment308
SubBScreening101
SubBPrep 1204
SubBTreatment308
SubCScreening101
SubCPrep 1204
SubCTreatment308
SubCFollow up 14011

 

As you can see, in the event of a tie, it skips over the next rank. What I want to know is how to get it so that it does not skip over ranks. 

I'm trying to create a distribution plot that tracks which visits each subject has attended, with spaces in the data points for visits the subject has not attended. The expression I'm currently using gets it so that all the visits appear at the same value, which is good, but not evenly spaced, which is not good as it isn't as easy to spot gaps. 

Thanks!

1 Solution

Accepted Solutions
sunny_talwar

So may be try this

Aggr(NODISTINCT Rank(Only({<VisitName -= {"Lab*"}>} VisitNumber), 1, 1), VisitName)

View solution in original post

8 Replies
sunny_talwar

How about this

Aggr(NODISTINCT Rank(VisitNumber, 1, 1), VisitName)
mpnewbie
Partner - Contributor III
Partner - Contributor III
Author

This won't work for me. I'm looking for something that can be used in a measure expression for a chart. 

My dimensions are VisitName and Subject.

sunny_talwar

Sorry, but why can't this work as a measure expression for a chart with VisitName and Subject as dimension?

mpnewbie
Partner - Contributor III
Partner - Contributor III
Author

My bad, I accidentally copied it over poorly.

It does work as a measure expression, but it doesn't do what I need it to. It's evened out some of the spacing, but it's still not even. Also it appears to start ranking at 52.

sunny_talwar

Are you restricting your chart using a calculated dimension or something similar?

mpnewbie
Partner - Contributor III
Partner - Contributor III
Author

Yes. Some of the visits that are present are "Lab 1" and so on.

So the VisitName dimension is actually:

if(not WildMatch(VisitName, 'Lab*'), VisitName)

sunny_talwar

So may be try this

Aggr(NODISTINCT Rank(Only({<VisitName -= {"Lab*"}>} VisitNumber), 1, 1), VisitName)
mpnewbie
Partner - Contributor III
Partner - Contributor III
Author

This worked perfectly! Thank you so much!