Dealing with ties in a Rank function

[mpnewbie]

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!

[sunny_talwar]

How about this

Aggr(NODISTINCT Rank(VisitNumber, 1, 1), VisitName)

[mpnewbie]

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]

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]

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 · Accepted solution]

So may be try this

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

[mpnewbie]

This worked perfectly! Thank you so much!