9 Replies Latest reply on Aug 1, 2018 9:14 AM by Greg Reinecke

# Golf Handicap Calculator-Rank, Select, Calculate

Hello All,

I am trying to create a golf handicap calculator (current workbook is attached). After generating handicap differentials for each week played I now need to select the lowest differentials and then calculate the average of those lowest differentials identified. Here is the current dashboard with Step3 and Step4 being just placeholders.

I have also included the explanation of logic to support Step3 and Step4 below:

.

Any help would be appreciated. It may well be my approach is all wrong so I'm open to any suggestions. All the best and thanks in advance.

• ###### 1. Re: Golf Handicap Calculator-Rank, Select, Calculate

Hi Greg,

hope it helps.

BR,

bharat

• ###### 2. Re: Golf Handicap Calculator-Rank, Select, Calculate

Bharat,

Thanks for taking the time. I’ve previously seen all the examples and they are awesome. Your first noted example by Daniel Baker seemed close but unfortunately I didn’t see an ability to download the workbook to learn from his expertise. The other examples seemed that they already knew their handicap so it need not be calculated.

The learning opportunity in this example for me is about how for a single person (name) can I  1)rank their scores from lowest to highest, then based on the number of scores or rows associated with their name  2)SUM the correct range of lowest to highest scores and then 3)AVG them based on the total scores used to support the SUM in 2.

Hmmmm

I’ll keep trying and thank you so much for your time.

Greg

• ###### 3. Re: Golf Handicap Calculator-Rank, Select, Calculate

Greg,

I'm not sure if I caught the gist, but please see if the below can give some ideas.

I made a count of the number of handicap differentials [Step2.5a-Number of Handicap Diffs]:

{ FIXED [Name]:COUNT([Step2-HdcpDifferential])}

// this can also be done as a table calculation:

// WINDOW_COUNT(SUM([Step2-HdcpDifferential]))

Then used that to see how many are to be averaged:

IF [Step2.5a-Number of Handicap Diffs]>=20 THEN 10

ELSEIF [Step2.5a-Number of Handicap Diffs]=19 THEN 9

ELSEIF [Step2.5a-Number of Handicap Diffs]=18 THEN 8

ELSEIF [Step2.5a-Number of Handicap Diffs]=17 THEN 7

ELSEIF [Step2.5a-Number of Handicap Diffs]>=15 THEN 6

ELSEIF [Step2.5a-Number of Handicap Diffs]>=13 THEN 5

ELSEIF [Step2.5a-Number of Handicap Diffs]>=11 THEN 4

ELSEIF [Step2.5a-Number of Handicap Diffs]>=9 THEN 3

ELSEIF [Step2.5a-Number of Handicap Diffs]>=7 THEN 2

ELSEIF [Step2.5a-Number of Handicap Diffs]>=5 THEN 1

END

Then Ranked the handicap differentials:

RANK(MIN([Step2-HdcpDifferential]),'asc')

with compute using of Name,WeekOfDate

restarting every Name

So Step3 became:

IF [Step2.5c-Rank Handicaps]<=MIN([Step2.5b-Count which Diff to Use])

THEN MIN([Step2-HdcpDifferential])

END

And Step4 became:

WINDOW_AVG([Step3-Handicap Value to Include])

with compute using of Name,WeekOfDate

restarting every Name

1 of 1 people found this helpful
• ###### 4. Re: Golf Handicap Calculator-Rank, Select, Calculate

Hi Greg,

BR,

bharat

1 of 1 people found this helpful
• ###### 5. Re: Golf Handicap Calculator-Rank, Select, Calculate

bharat,

Thanks for the help. I downloaded some examples that were very close. Swaroop was a huge help. I have it all just about done. Mind you my approach is likely very inefficient (see workbook attached but it does seem to work. My last ??? have to do with 1) Totaling the top "9" weekly pts scores (currently a filter) by player and have the total number of filtered weeks displayed for each player and 2) rank the best weekly point totals (highest weekly total as #1 to the lowest of the filtered 9 weekly totals as #9. Thanks again everyone!

• ###### 6. Re: Golf Handicap Calculator-Rank, Select, Calculate

Greg,

Apologies for the delay.

Please see if the below can give ideas for the next parts.

You can try a flag for whether to include a top 9 or not using:

IF [Top Pts By Week]<10 THEN "Include"

ELSE "Exclude"

END

And then get the total using:

WINDOW_SUM(IF [Top9status]="Include" THEN [NetScore] END)

You can count how many excluded using:

WINDOW_SUM(IF [Top9status]="Exclude" THEN 1 ELSE 0 END)

The table calculation settings for most of these are

Compute Using: Specific Dimensions: Player, Day of Play

Restarting: Player

• ###### 7. Re: Golf Handicap Calculator-Rank, Select, Calculate

Yep. That did the trick!  Thank you so much.

Back to my earlier comment I think I made this more complex then it need be. When I want to create a bar chart showing total pts by player things seem to get tricky. I can't get an accurate per player chart based on the year 2018. I have added my working dashboard without any cool charts just incase I haven't worn out my welcome.

• ###### 8. Re: Golf Handicap Calculator-Rank, Select, Calculate

Greg,

Glad the calculations have been helping.

Definitely feel free to ask more questions.

Happy to take a stab at it.

With regards to the graphs, there is indeed the

trickiness associated with these calculations

that multiple copies of the Total get stacked together.

One way to filter out the duplicates is to place

a calculated field of just INDEX() on the Filter shelf.

Initially, it will probably just range from 1 to 1,

but when you set the Table Calculation settings

to match that for [Total Pts], then it should give you

a range from 1 to something like 11 which is saying

that something in the set has 11 rows and so it is

creating the total 11 times.

Once you filter this Index down to just one, you should only

get one copy and a reasonable looking graph.