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

# Count occurances of calculated field

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

• ###### 1. Re: Count occurances of calculated field

Create two calculated fields:

a. Winner

{FIXED [Team],[Race]: MAX( IF [Position]={FIXED [Team],[Race]:MIN([Position])} THEN [Name] END)}

b. Count

IF [Name]=[Winner] THEN 1 ELSE 0 END

Drag Name and Count to View.

Hope this helps.