Skip to Content
avatar image
Former Member

Rank Function

In terms of the Rank (Most Recent column) -I need the largest IID to list first when there is a tie in the date.

=Rank([Date];[IID];[Case Number])

I have tried

=Rank([Date]; ([IID];[Case Number]);[Case Number]) as well.

What am I doing wrong here?

ddwdk.png (8.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Feb 21 at 10:47 AM

    Hi there,

    When you say list first -- are you trying to sort so the largest IID value is first in the list?

    or do you want to only show the largest IID value for a given date?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi James,

      Sorry. To clarify, in terms of the RANK the data for 7 and 8 should be swapped because IID 1565354 is more recent than 1526442.

      I even have the date and the iid sorted descending, but the IID doesn't budge.

      Make sense?

  • Feb 21 at 11:07 AM

    follow these steps.

    Sort date is in descending.After that sort IID in descending.

    use formula =RunningCount(Rank([Date]))

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      What you are suggesting is probably more simple than doing the rank and will consider in the future but I have copied it as you have it (substituting my fields) and it still doesn't reset at change of case number.

  • avatar image
    Former Member
    Feb 21 at 11:41 AM

    Apparently what I thought was a DATE field, even looked like a dd/mm/yyyy date field actually has something else which skewed the sorting which is why sorting by date descending had no affect on the IID being sorted descending.

    I created a variable to ensure the date was converted and went back to my original RANK

    =Rank([Date];([Date];[IID]);[Case Number])

    and this works now.

    Add comment
    10|10000 characters needed characters exceeded