1 Reply Latest reply on Oct 21, 2018 7:52 AM by Ombir Rathee

    Count occurances of calculated field

    Victoria Bennett

      Hi,

       

      I've been trying (and failing) to calculate the number of times one person beat another in a race in a year.

       

      I can't upload my data, but it's basically.

       

      Race      Team          Name          Position

      1               1               Name 1          3

      1               1               Name 2          1

      1                2               Name 3          2        

      1               2               Name 4          4

      2               1               Name 1          4

      2               1               Name 2          1

      2                2               Name 3          3        

      2               2               Name 4          2

       

      What I want to do is count how many times a person finishes ahead of their team mate?

       

      For the example above I'd expect

      Name 1 = 0

      Name 2 = 2

      Name 3 = 1

      Name 4 = 1

       

      I could work out the difference

                ZN(SUM([Position)])) - LOOKUP(ZN(SUM([Position)])), 1)

       

      But then i couldn't work out where to go from here.

       

      Any help will be gratefully appreciated.

       

      Thanks