Analyzing IPL Stats with Tableau — Part 2
This is a continuation from Part 1 in the series, where we discussed the steps involved in data preparation, then slowly built on that and discussed the first page of the report which focused on the season level information of IPL.
Now we will understand other Tabs in the report sequentially. So the overall flow of the second page report, which focuses on match level information, can be better understood using the flow chart below.
The values are fixed at various levels to show Team/Match Highest Scorer/Wicket Taker in the top KPI section. There is also a Match Result section here that summarizes result of a match selected in a single line.
IF [Toss Winner] = [Winner] and [Toss Decision] = ‘bat’ THEN [Toss Winner] + ‘ won the toss and chose to bat first. ‘+[Winner]+’ won by ‘+STR([Win By Runs])+’ runs.’
ELSEIF [Toss Winner] = [Winner] and [Toss Decision] = ‘field’ THEN [Toss Winner] + ‘ won the toss and chose to bowl first. ‘+[Winner]+’ won by ‘+STR([Win By Wickets])+’ wickets.’
ELSEIF [Toss Winner] <> [Winner] and [Toss Decision] = ‘bat’ THEN [Toss Winner] + ‘ won the toss and chose to bat first. ‘+[Winner]+’ won by ‘+STR([Win By Wickets])+’ wickets.’
ELSEIF [Toss Winner] <> [Winner] and [Toss Decision] = ‘field’ THEN [Toss Winner] + ‘ won the toss and chose to bowl first. ‘+[Winner]+’ won by ‘+STR([Win By Runs])+’ runs.’
END
There are following two major calculations in Fall of Wicket Section
Dismissal Over
IF [Dismissal Kind] =’bowled’ OR
[Dismissal Kind] =’caught’ OR
[Dismissal Kind] =’caught and bowled’ OR
[Dismissal Kind] =’hit wicket’ OR
[Dismissal Kind] =’lbw’ OR
[Dismissal Kind] =’obstructing the field’ OR
[Dismissal Kind] =’retired hurt’ OR
[Dismissal Kind] =’run out’ OR
[Dismissal Kind] =’stumped’
THEN STR([Over]-1)+’.’+STR([Ball])
END
Dismissal Status
IF [Dismissal Kind]=’bowled’ THEN ‘b ‘+[Bowler]
ELSEIF [Dismissal Kind]=’caught and bowled’ THEN ‘c & b ‘+[Bowler]
ELSEIF [Dismissal Kind]=’caught’ THEN ‘c ‘+[Fielder] +’ b ‘+[Bowler]
ELSEIF [Dismissal Kind]=’lbw’ THEN ‘lbw b ‘+[Bowler]
ELSEIF [Dismissal Kind]=’run out’ then ‘run out ‘+[Fielder]
ELSEIF [Dismissal Kind]=’stumped’ then ‘st ‘+[Fielder] +’ b ‘+[Bowler]
ELSE ‘ ‘
END
Note here that we have subtracted 1 from Over in calculation Dismissal Over as the over count begins from over 1 in data and not 0, as it should ideally.
The Third Dashboard dwells on the performance of individual players categorizes as Batsmen and Bowlers. The Metrics to bring this comparison are calculated as below.
6s and 4s: This shows the composition of runs scored by batsmen indicating the hitting capacity.
IF [Batting Team]=[Teams] THEN
(
IF
{FIXED [Id],[Batting Team],[Overs]:[Batsman Runs Adjusted Team1]} = 6 THEN ‘6s’
ELSEIF
{FIXED [Id],[Batting Team],[Overs]:[Batsman Runs Adjusted Team1]} = 4 THEN ‘4s’
ELSE
‘Rotating’
END
)
END
Chasing/Scoring: This puts the runs scored in two buckets — Chasing and Scoring which in turn tells the performance of batsman in the two situations.
IF [Inning]=1 THEN ‘Scoring’ ELSEIF [Inning]=2 THEN ‘Chasing’ END
Winning Contribution: This is a view of contribution of batsmen to the team’s victory. This is being evaluated by calculating the number of times the player’s team has won and the percentage of those where the player was awarded Man of the Match.
Dismissal Pattern: Here we look at the pattern in which a batsman has been dismissed in the past.
In the bottom section, we look at the number of 50s and 100s that the batsmen has aggregated
50s:
SUM(
IF {FIXED [Batsman],[Id]:[Batsman Runs Adjusted Team1]}>=50 AND {FIXED [Batsman],[Id]:[Batsman Runs Adjusted Team1]}<=99
THEN
1
ELSE
0
END
)
100s:
SUM(
IF {FIXED [Batsman],[Id]:[Batsman Runs Adjusted Team1]}>=100
THEN
1
ELSE
0
END
)
In this last section, we understand the tightness in bowling of bowlers, by measure the Economy Rate which is the number of dot balls bowled out of the total balls bowled.
Number of Dot Balls:
COUNT(IF [Batting Team]=[Teams]
and ([Total Runs])=0
THEN [Ball] END)
In the final section of Report, we talk about the performance of teams from the perspective of Home Ground Vs Away and Toss Effect.
Home Ground Vs Away: Some IPL teams have 1 Home Ground where as some have multiple. This is the reason we need to create a group of Venues based on the official Home Grounds of Teams.
Then we create a classification of teams as Home and Away.
IF [Teams] =’Royal Challengers Bangalore’ THEN
(IF [Venue (group)] = ‘M. Chinnaswamy Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF ([Teams] =’Delhi Daredevils’ OR [Teams] =’Delhi Capitals’) THEN
(IF [Venue (group)] = ‘DC Home Grounds’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Mumbai Indians’ THEN
(IF [Venue (group)] = ‘Wankhede Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Kolkata Knight Riders’ THEN
(IF [Venue (group)] = ‘Eden Gardens’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Rajasthan Royals’ THEN
(IF [Venue (group)] = ‘Sawai Mansingh Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF ([Teams] =’Deccan Chargers’ OR [Teams] =’Sunrisers Hyderabad’) THEN
(IF [Venue (group)] = ‘Rajiv Gandhi Intl. Cricket Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Chennai Super Kings’ THEN
(IF [Venue (group)] = ‘M. A. Chidambaram Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Pune Warriors’ THEN
(IF [Venue (group)] = ‘Dr DY Patil Sports Academy’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Kochi Tuskers Kerala’ THEN
(IF [Venue (group)] = ‘Nehru Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Rising Pune Supergiants’ THEN
(IF [Venue (group)] = ‘Maharashtra Cricket Association Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Kings XI Punjab’ THEN
(IF [Venue (group)] = ‘KXIP Home Grounds’ THEN ‘Home’ ELSE ‘Away’ END)
ELSEIF [Teams] =’Gujarat Lions’ THEN
(IF [Venue (group)] = ‘Saurashtra Cricket Association Stadium’ THEN ‘Home’ ELSE ‘Away’ END)
END
Note that there are times when the season has been hosted abroad, hence these matches too have been clubbed as Away for a team. Finally the view gives a picture of teams’ performance when at Home as compared when playing outside.
Toss Effect: To analyze the effect of Toss and the decision to bat or bowl first on the outcome of matches, we create following calculation.
Toss Win Lose:
IF [Teams]=[Batting Team] and [Teams]=[Toss Winner]
THEN
(
IF [Toss Decision] = ‘bat’ THEN ‘Won Toss and Bat First’
ELSEIF [Toss Decision] = ‘field’ THEN ‘Won Toss and field First’
END
)
ELSEIF [Teams]=[Batting Team] and [Teams]<>[Toss Winner]
THEN
‘Lost the Toss’
end
This marks the end to the explanation series on IPL Analytics.
