
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dealing with ties in a Rank function
Hi,
Given the following data:
Subject | VisitName | VisitNumber |
SubA | Screening | 10 |
SubA | Prep 1 | 20 |
SubA | Prep 2 | 21 |
SubA | Treatment | 30 |
SubB | Screening | 10 |
SubB | Prep 1 | 20 |
SubB | Treatment | 30 |
SubC | Screening | 10 |
SubC | Prep 1 | 20 |
SubC | Treatment | 30 |
SubC | Follow up 1 | 40 |
How do I get to the following table:
Subject | VisitName | VisitNumber | Rank |
SubA | Screening | 10 | 1 |
SubA | Prep 1 | 20 | 2 |
SubA | Prep 2 | 21 | 3 |
SubA | Treatment | 30 | 4 |
SubB | Screening | 10 | 1 |
SubB | Prep 1 | 20 | 2 |
SubB | Treatment | 30 | 4 |
SubC | Screening | 10 | 1 |
SubC | Prep 1 | 20 | 2 |
SubC | Treatment | 30 | 4 |
SubC | Follow up 1 | 40 | 5 |
I am currently using the following expression:
rank(TOTAL -aggr(DISTINCT VisitNumber, VisitName, Subject), 1, 1)
Which gives me the following:
Subject | VisitName | VisitNumber | Rank |
SubA | Screening | 10 | 1 |
SubA | Prep 1 | 20 | 4 |
SubA | Prep 2 | 21 | 7 |
SubA | Treatment | 30 | 8 |
SubB | Screening | 10 | 1 |
SubB | Prep 1 | 20 | 4 |
SubB | Treatment | 30 | 8 |
SubC | Screening | 10 | 1 |
SubC | Prep 1 | 20 | 4 |
SubC | Treatment | 30 | 8 |
SubC | Follow up 1 | 40 | 11 |
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!
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So may be try this
Aggr(NODISTINCT Rank(Only({<VisitName -= {"Lab*"}>} VisitNumber), 1, 1), VisitName)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this
Aggr(NODISTINCT Rank(VisitNumber, 1, 1), VisitName)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, but why can't this work as a measure expression for a chart with VisitName and Subject as dimension?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you restricting your chart using a calculated dimension or something similar?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So may be try this
Aggr(NODISTINCT Rank(Only({<VisitName -= {"Lab*"}>} VisitNumber), 1, 1), VisitName)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This worked perfectly! Thank you so much!
