1 2 Previous Next 25 Replies Latest reply on Jun 22, 2016 5:15 PM by Ivan Young

# Aggregate Top N measures per Player

Hey everyone!

I'm new to the Tableau community but a Tableau user at work for a year now.  I've used these forums over the past year to answer a ton of my questions.  I have run into a problem that I'm hoping to solve with your help.  I haven't found the answer through my search.

I currently have a data set of sports players, their games by game date(and game ID) and their statistics at the game level.  See example below.  What I'm looking to do in Tableau is to provide a summary of the statistics for each player for the last N of their games played.  So lets say I want to the last 3 games for each player.  So the output should look like the example below.  I've tried using the Index() and Rank() functions but once I start to introduce the measures(Points, Rebounds, Assists) the rank or index change and I can't find the right combination with table calculations.  The part that seems to be difficult is that the Game Dates will be different for each player when looking at last N.  As you can see, Lebron has played more recently than Kobe, so I want the numbers from his last 3 games, which are in June, AND Kobe's last 3 games, which are in May.  This N needs to be dynamic as selected by the user.  They may want the last 3 games, or 10 or 20, etc.

Example Output:

Player | # of Games | Points | Rebounds | Assists

Lebron          3              94             27             24

Bryant           3              84             14             11

Example:

Player | Game ID | Game Date | Points | Rebounds | AssistsLebron  6172016    6/17/2016       30             7              10

Lebron  6122016    6/12/2016       24            10              8

Lebron  6052016    6/05/2016       40            10              6

Lebron  6012016    6/01/2016       21            9                8

Lebron  5122016    5/12/2016       32            7                9

Bryant   5122016    5/12/2016       30             7              4

Bryant   5072016    5/07/2016       34             4              5

Bryant   5052016    5/05/2016       20             3              2

Bryant   5012016    5/01/2016       21            9                8

Bryant   4122016    4/12/2016       32            7                9

Appreciate the help!

• ###### 1. Re: Aggregate Top N measures per Player

Mahfooj Khan, khalid norat can you assist in this?

• ###### 2. Re: Aggregate Top N measures per Player

Hi there,

You want to be using an index calculation and sorting by date of the game. You then need to compute using Pane, across then down

I hope this helps:

Kind regards,

Paul

• ###### 3. Re: Aggregate Top N measures per Player

Hi Paul,

I don' think this will work since in the final view we don't have dates. You can't use index or rank in this case.

We need Max Match date by player and 3rd last game date by player. Then we can create a date range using which we can simply filter the data.

-Ashish

• ###### 4. Re: Aggregate Top N measures per Player

Hi Ashish,

You don't need the dates in the final view to use this filtering method. You simply need to untick show headers on the date pill and it will allow it for use in calculations without showing it (Please see below)

I hope this helps

Kind regards,

• ###### 5. Re: Aggregate Top N measures per Player

Hi Ashish,

I think I realise what you mean now, the second table caused me to focus on it but you actually just want an aggregation of the last 3 games per player..

Let me take another look at it

• ###### 6. Re: Aggregate Top N measures per Player

PFA.

-Thanks

• ###### 7. Re: Aggregate Top N measures per Player

That's the twist here my friend.

• ###### 8. Re: Aggregate Top N measures per Player

Hello,

Unfortunately I can't figure out how to make this work. The best I came up with was to hide the rows so that you have an aggregation, but then the dynamic filtering doesn't work..

I'll have to step off and leave it to a guru

Kind regards,

• ###### 9. Re: Aggregate Top N measures per Player

Shawn sir, any thoughts on this? any guidance will be appreciated. I tried doing it many ways but couldn't manage it.

• ###### 10. Re: Aggregate Top N measures per Player

I'm able to get the last three gameId based on max date but unable to agg the measures

• ###### 11. Re: Aggregate Top N measures per Player

I think the quick and dirty solution to solving this would be to add a rank field to the source data

you can then use this field as a filter in tableau to choose how many games to aggregate.

As to achieving this in tableau I will have a little play but i agree with Ashish Chaudhari this is going to be a tough one

• ###### 12. Re: Aggregate Top N measures per Player

same here Mahfooj. I have also reached till this point but unable to understand how to create date range which will filter the aggregated data. How did you managed to get this last 3 games? I tried doing last() and index but then realised table calcs won't help me when I try to aggregate things.

• ###### 13. Re: Aggregate Top N measures per Player

Not sure. we will keep on discussing this, suddenly tableau champ will come and boom! He will make it look very simple.

• ###### 14. Re: Aggregate Top N measures per Player

Im not too hopeful on this one. Without doing prior work on datasource.

Hope your not referring to me as Tableau Champ and you have someone else that's gonna swoop in and help us.

1 2 Previous Next