Transcription
Hey guys, welcome back to my channel. So, in today's video, we will see the design and development of this beautiful dashboard from start to end in Tableau Software. In our today's topic for this design is Road Accident Analysis. The data which we are using, uh, it is a .csv file which we have taken it from the Kaggle website. And the website has n number of datasets, different domains are there, and today we have chosen the accident domain. And the data which we are having, it is in Excel file. I will show you the data as well.
Before that, the data which we are using, it's a completely demo data. It's not a real-time data, and we are not taking any data from any organization or any of the government websites. It's completely taken from Kaggle where we have a dummy data, and I have made some modifications as well in the data. I have cleaned the data as per our use, and then we have used that particular data into our visualization. So, you can see this beautiful dashboard. It is completely dynamic and interactive.
So, so at the top, if you can see, we have used three filters over here. First one is the current year, next is the previous year, and then we have chosen the severity, and that is accident severity. Uh, if you choose, we have these three filters. And, uh, looking for, uh, the overall dashboard, if I explain different components on here, you can see at the top, you can see a title. And then on the left side, in vertically arranged, we can see these are the overall KPIs which gives us total accident, total casualties, and in in that casualties, they are subdivided into fatal, serious, and slight casualties. Then we have a small numbers with an arrow showing the year-on-year increase with respect to current year and with respect to previous year.
Similarly, if you can see at the top, in the horizontal section, we can see there are the casualties by vehicle type, and there are different vehicle types which you can see here, like agricultural vehicles, vans, bus, cars, motorcycles, and these are the other vehicles which data is not mentioned in there. And here also, there are some year-on-year growth values are present in this particular chart as well, which indicates the values with respect to current year and previous year, how they are increasing or decreasing. Then we have two donut charts which gives us based on the, like, the casualties by weather condition and by road surface. We have a bar chart which gives us the casualties by road type. And, uh, the last one is the map chart which gives us the casualties by the location. And, so we will see the step-by-step creation of each and every chart and KPI, how to do this.
So, I will, um, I will operate you, uh, the dashboard, and you can see the changes. See, made. You can see the current year and previous. Everybody we have chosen here is the current year. We can choose dynamically how which current year we want to see and we can analyze our different numbers in this particular dashboard with respect to a dynamic previous year. Let's say, like, right now, by default, it is showing current year 2022 and 2021 as the previous year. Let's say I want to change this current year. I will make it as 2021 and I want to compare it with 2020. All right, so you can see all the values will change with respect to that, and you can compare 2021 with respect to 2020. If you want to see 2019, though, what we can say, the area chart here, the sparklines which we have, they will change and they will give the percentage values as well with respect to that particular year.
So, I will come back again to 2022. And now, right now, it is comparing you 2022 and 2019 as a previous year, right? And if you can see the current year, right now, all the numbers which we are showing, uh, in bold are right now all the numbers which are there with respect to current year, right? And we can change our current year with respect to our requirement, right? And all the dashboard values will change with respect to that. The last, uh, last one is the select accident severity. Right now, it is chosen as a fatal. When I choose serious, you can see all the values and the color combination of the dashboard will also change with respect to that particular severity. And you can see, uh, there are different, like, uh, titles will also change with respect to, uh, whatever the filter we have chosen here. Like here, it is a serious everywhere, uh, the series will, or the name will be changed, so that a user can know, yeah, which, like, the data is shown for which casualty type, right? So, now when I go for a slight, you can see the values, uh, the color will again change. And when I select all, all means nothing but the values or the data will be chosen for all types of severities combinedly. The data will be shown here, and the color combination will also change with respect to that. That's why I will go again back to series.
And if you can see, I have also used some action filters over here. So, let's say you want to see, uh, the data with respect for car only. So, when I click on car, you can see the entire dashboard will change, and it will give us the data with respect to cars. If you want to see for goods vehicle, when I click on Goods vehicle, you can see it will change and it will give us the value for vehicle type as a goods vehicle. All right, in this way, we will be using our dashboard. Similarly, these are also, uh, dynamic. When I click on any part, so if you, you will get the values with respect to that particular weather condition. So, I want to see for a dry surface, you can see we will be getting it for dry surface. We want to see for different road type, like we want to see how the casualties, serious casualties are there for, uh, what we can say, dual coverage way or road type. So, when I click, you can see values will change, and with respect to that, we can get the deeper knowledge of this particular data, right? So, in this single dashboard, you can analyze different things, and you can give your user a different experience to see the data from different angles, right? So, this is very important. The data must be seen in different angles, in different, uh, what we can say, for different situations and different scenarios, right?
So, uh, before starting, I will show you what data we are using here. You can see this is an Excel CSV file which has been taken from Kaggle website. Again, I am telling you, it is not a real-time data, it's a dummy data, and we have made some changes as well in that, right? So, you can see, uh, if, if you see the size of data, when I click here and if we get the count, it's almost 6 lakh counts is there, right? So, it's almost 6.6 lakh, like 0.6 million rows are there. And if you see the columns, we have 14 number of fields, that is which will be used in our particular dashboard design. There are many more, I have deleted because we are not using that and that are not important as well. And, uh, yeah, so this is what we have right here. We have a data from, if you can see, the data is from 2019 to 2022. The dates have been also changed. Those are not real-time, it's a dummy data. Again, and there are a number of columns.
So, first thing first, guys, always remember whenever you are starting any new visualization, let it be in any software like Power BI or Tableau or Excel, as I always tell you, you must study the data first, right? Study of the data is very much important. If you are analyzing your data properly, studying it and finding the granularity of the data, how is the data flowing from higher level to the lower levels, your 50 to 60 percent of work is or work of visualization is done over there itself. When you study data, you will get an idea like what visualization should be done, what the output can be, or what insights can be taken out of from this out from this data. And its next part is only you have to use a tool, and you can use any tool. It's, let me, it may be Tableau or Qlik Sense or Excel, or you are using Power BI. It's just that you have to visually represent it in front of your customer, right? So, data will remain same, visualization tools will change from, like, A to Z. Like, there are many visualization tools available in market right now. But for today, we will see it in Tableau. And you, if you guys are interested to see this particular visualization in apart from other than Tableau, you can mention your views in the comments. Uh, if you want the same in Excel or you want it in Power BI, let me know. I will make a video for that as well. So, for today, we will be seeing in the Tableau.
And before starting, guys, I like this, this particular thing takes lots of effort, like and like searching the data, designing the backgrounds, designing whatever we are doing, some in charts, calculations, many more. So, I, I request one effort from you as well. You can go ahead and like this video, subscribe to the channel, and turn on the notification bell if you are learning something good from this particular video series which I am doing here. And it will give me a motivation as well, or to make many more videos in future as well. All right, so before wasting time, what we will do, I will start designing our dashboard. For that, I have taken already a new sheet. But if you can see here, the data is very much large. And if you want to use the same data, I will put a link from where you can download. So, the data is almost of 90 MB. So, I will compress it and then I will paste, like, I will add a link here. You can go ahead and you can use any, like, what we can say, the tool where you can extract your data and then, like, the, the compressed size which I will be using, it will be about, for you to download, it will be about 10 MB. And the size when you open it or extract it, it will be about 90 MB at this particular file because the data is very much big. And then you can use that, right? So, we will start our design.
And for that, I will take a new workbook. So, I will go ahead and open this. So, this is my new Tableau workbook which I have taken. So, first thing first, what we have to do, we have to connect data. So, there is an option here. I will click on this connect to data. And it, as it is a CSV file, it's a flat file, it's not Excel file, it's a CSV file. So, we have to click on text file. And I have my data here. So, this is my data. You just have to click OK. And we have this particular file which will be already because we have single sheet. So, if, if it doesn't come, you have to drag it from here into your, what we can say, logical data pane here. So, there are two, what we can say, data pane which we have here, that is a logical window and next is what physical layer and logical layer. So, here it is a logical layer where we connect our different tables with relationship nodal and inevitable. Click here, we will be using here as joins, like inner, left, outer, and whatever we have, right? So, now if you can see, uh, if I click on update now, here you can see the data will be viewed, like first 100 rows we can view. And you can see if the data is correctly populated, if there is any error in this particular data, or if any field is not been populated correctly. And then you have a metadata over here. You can check the data types, if, correct data type has been assigned to all the fields which we have, like longitude and latitude must be assigned with geographical data type. So, it is correctly assigned. You can check if it is not, and you can change with respect for that.
And as the data size is very much big, guys, we will be not using live connection. We will use extract connection. The reason behind this is, uh, the data is, um, like more than six lakhs or 0.6 million. So, what, what will happen if you are using a live connection? Whenever we are building a view, visuals, Tableau will fire a query to our dataset, which is a CSV file, and which will, like, it will collect, aggregate the data into, will show us into the visualization. And this takes few seconds or, and it increases the load time in Tableau as well. So, what we will do, we will create an extract. When you create extract, then hyper file will file will be created where Tableau will directly fire query to that particular .twbx file or hyper file, where it will be very faster and it will give us the result faster. It will not go, fire the query to our database, that is the CSV files. So, we will create an extract connection because the data is big, so that our, what we can see, visualization should be, you know, loaded fastly, and we, we will be getting a data instantly on our library visualization or Tableau charts. So, I will create an extract.
So, as soon as you hit the Sheet1, so when I click on Sheet1, it will ask you to save your hyper file. So, I will name the chain, change the name as accident data, hyper file. I will name it as hyper file, okay? And I will just save it. You can save it anywhere. And then you have to click on Sheet1. So, as soon as you click, so it will, you know, it will extract all the data which you have in CSV file and it will store it in, in, what we can say, that particular file and it will bring us into new sheet, right? So, now whenever we are building anything, let's say whenever we are taking an accident date, so what, what does Tableau do? That Tableau will fire query to that particular hyper file where data is stored in compressed size, right? It will not store in actual size. The data will be compressed and stored over there, and it will give us the result faster, and it will be low, the loading time will be very much higher, the performance of Tableau will increase, right? So, this is the reason we have used the extract connection. This is a learning for you. The interview questions are asked on this enormous times, right? So, many times the interview question will ask you, what is the difference between live and extract connection? Perfect.
So, now we have extend year here. So, so the first thing first, what we have to do is, if you go, what we will design first is, we will design all these KPIs over here. Uh, we will design this overall KPIs. We will design the sparkline for each and every KPI which we have shown here. The first we have to design is the total extent. So, I will rename the sheet and I will name it as total accidents. Perfect. So, this is the total accident. Right now, what we will do, we will for writing the current, we want the accident year for that current year, right? Or we will be using one filter over here. You can see current year, and we should have a ability to select our dynamic, like current year, uh, whichever we want our user want, right? So, that particular current year. This is actually a parameter. So, what we will do, we will click here and we will first create a parameter and I will name it as current year. All right, so now what I have to do, uh, I have to choose a data type and I will choose it as integer, because those numbers are actually 2021, 2022, those are integer forms, right? And, uh, right now, what we want, we want it is list. And I want to take it from actually the year. We want that particular year should be dynamic. Let's say, right now we have a data until 2022. So, whenever new data is added and we get a new year, that is 2023, we don't have to go into that parameter and, you know, edit the parameter and add our for a parameter value manually, that is 2023. So, what we can do, we can choose our parameter dynamic in such a way that whenever a new year is added, the parameter will update automatically. Where that particular value or that year will come into that parameter automatically, and we can, you know, use that particular parameter value. So, for that, what we can do, if we, we have to choose a list and we have to take it from that particular add values from that field. But we are not seeing that accident date here, right? So, what we have to do, if we choose a date as a data type, and you can see, you will get an accident year date here. But you, you can see your date is in the form of full date, right? Or you can see it's from ranging from the first date of 2019 up to end of that particular 2022. We, we want here to be selected, right? So, what we will do for that, we have to write first calculation. So, I will go ahead and write your calculation field and I will name it as year of accident date. Okay, so here I will write function as year and I will, I just have to extract the accident digit here, right? So, I will right-click here, year of accident and click on OK. So, when I'm bringing this into rows, you can see, and I will just make it as discrete. So, it is giving us a sum. But when I take it into text, and let's say, first, what we will do, first, we will convert this into discrete, convert this to discrete, and then convert this to dimension, okay? And when I bring this into rows, you can see, we are getting year as 2019 to 2022. So, the thing is here, like whenever new year will be added into this accident date, so this calculation will update and it will show us as 2023. Now, we will go ahead and create our parameter and we will name it as current year. Okay? And we want it as integer. And we want it from a list value. And we will choose it from year of accident date. You can see automatically whatever the data this particular field is having, that is year of accident, all the values will be populated. Or it is taking the value from that particular field, and you can see all the values here, right? And this is the value. And whatever the display value will be there, that is in front of a user, in front of as an UI, what you, in front end, what you will be displayed, 2019. But you can see there is a comma here. So, what we will do, we will just delete the comma from each of the values, right? So, I will just delete that particular comma. Perfect, right? And what should be the current value? So, the current value for a current year, it should be the latest value. So, we will choose it at 2022. What does current value means? That whenever you will close the workbook and you will open it again, the 2022 will be the default value for this particular parameter, right? So, this is the current year. I will just click on OK. You can see the parameter has been created over here. So, I will just right-click and I will duplicate it, because we want the same parameter values for previous year, right? So, I will just duplicate it and I will edit this parameter and instead of current year, we will name it as previous year. All right, so this is previous year. And whenever the workbook will open, the current value should be less than 1 as compared to current year. So, if current year is 2022, current value for previous year, it should be one year back, that is 2021. And this is all the same, because we are taking the value from year of, uh, accident date, which is dynamic. And you just, we will click on OK. Perfect, so we have our two parameters. And now we will start and design our total accident KPI or develop and so we will just click on new calculation and we will name it as current year accidents. All right, and in this case, we have to write the calculation dynamically in such a way that whenever a current year is chosen, that particular value should be updated dynamically. So, for that, what we will do, I will be using an IF condition. IF year of accident date IF year of accident date is equal to current year, current year. Okay? If it is equal to current year, then we want an index. Okay? So, index is nothing but whatever this index which we have here, this index is nothing but the index given to our dataset, right? So, if you can see, I will show you the data here. So, this is the index, and this is a unique number for each and every row, right? So, it is a unique number for each and every row. So, we will be using here, and we want it to be closed, right? So, this is our calculation. And what we want, we want the aggregation as count. So, we want count of all this. So, I will put an aggregation as count and we will open and close parenthesis. We will add to this particular calculation. I will just click OK and I will take this and bring it into visualization. So, you can see these are the total current year accidents. And when I click on this parameter and click on show parameter, so whenever I am changing it to 2021, you can see the accident value will be changing with respect to that selected parameter, right? So, because we have what calculation we have written here is, when I click on edit, the year of accident date should be equal to current year. So, whichever current year we will be choosing here, let's say right now it is 2022, so it will go ahead and ask this particular accident date should be equal to 2022. When we choose it as 2021, it will go ahead and change this accident date to 2021 and it will give us the count of this particular index into this particular visualization. I hope you have understood this. So, if you understood this, the previous year is very much simple. Just click on OK. And next, what we will do, I will just duplicate this calculation. We will be using same cell calculation. I will click on edit and instead of current year accident, I will mention here as previous year accidents. Right? And instead of current year, this previous year accident should be operated by using previous year parameter. This parameter which we have already designed. And I will just click on OK. And these are the previous accidents. When I bring this into text, and I will show this parameter, and whenever I am operating this, you can see the value that is second value is changing, that is for previous year, right? So, in this way, we have created our total accidents. The next thing what we have to determine is the year-on-year change in our total accidents. So, for year-on-year change, I will write one more calculation. I will name it as year on year accidents. Right? And the formula, we know that for to determine the year-on-year growth, we we take like current year sales or current year accidents minus previous or accidents and total divided it by previous year accidents. So, first, we will take as current year accidents and I will just add a parenthesis here, open your parenthesis. So, current year accidents minus previous year accidents. Sorry, it should be previous year accidents and divided by previous year accidents. All right, so this is the calculation we will be using. I will just click on OK. And I will bring this into text. And you can see, uh, when we are comparing 2022 with respect to 2021, the year-on-year growth of accident is minus 11 percent, right? So, we have to convert it into percentage. And you can see that if it is decreasing the accident or decreasing year-on-year, so first value which we see here, it is for current year, that is for 2022. And the second is for 2021. So, the current year accident here has been reduced with respect to last year. And if it is reducing in this particular case, it is good for us or it is good for that particular country or state or government. Because if the accidents are reducing, it is, it is a good thing, right? So, accidents are reducing means we do not have vehicle damages, no casualties are there, right? So, this is good. And what we can do for this particular thing, that is year on year accident, I will go ahead and change its default properties. Right? So, I will change the default properties of field itself. So, whenever we have to right-click on this year on year accident, then you have to go in default properties and you have to click on number format. And we will write here our own custom format, right? And in this custom format, what I will write is, you want here an upper arrow to be displayed. So, what we will do, first, I will write to 0.00 and we want it to be first displayed as percentage. Why 0.00? Because we want two decimal points. And we will add a semicolon and again 0.00 percentage. Okay? So, first one is for positive and second is for negative. So, I will just click on OK. You can see it has been converted to 11.70 percentage. But it is negative, it is not showing a negative because for both positive and negative, we have chosen it as it should be as 0.00 and 0.00, not negative or not positive. So, what we have to do, we have to add arrow over here, right? So, if you can see in our dashboard, you have to arrow this, use this down arrow or up arrow. If it is going down, like if it is negative, we should add a down arrow. And if it is positive, we should add a pair. So, for that, what we will do, I will just open an Excel file over here. So, we have a new Excel file and I will go in insert, then you have to go in symbols over here, right? If you click on symbols in insert, you can, you have to find this particular symbol, right? A parent down arrow. And if you want to use some another arrows, you can use that as well. It doesn't matter. So, I will just click on this and I will click on insert. Then I will click on this and I will click on insert. And I will close this. And I will copy this, Ctrl C. I will come back to our Tableau and I will just again right-click on this particular, go into number format and I will paste it over here, Ctrl V. Okay? And for this particular, I will just Ctrl X this and I will add it in front of this, right? So, whenever it is positive, it will show it us the value in up arrow. Whenever it is negative, it will show it in the form of down arrow, right? So, it is negative right now. So, when we click on OK, you can see a down arrow is added to this particular, what we can say, KPI. So, now we will design our KPI with respect to the font we want. The previous X here accidents, we are not showing this. So, I will just delete this. I will click on apply. And the name which we want to display here, it is a total accidents. Okay? And for this, I will make it as 12. And I will make it as semi bold. And I will choose the color as, let's see, this. Okay? And for this, I will choose it as white or for now, I will just click, it is black only. So, you can see the numbers what we are designing here. And I will choose it as 20. And we will be using as Tableau folder. Similarly, here you will be using this particular color. So, this one, this particular color. And I will choose this again. And I will make it as a 9. And I will choose it as Tableau bold. Click on apply, OK. And next, I will just change speed alignment to center. And I will change to enter view. So, you have designed this. So, one more thing here, you have to change here is, I will add a text over here that is year on year. And okay, right? So, this was our first KPI, that is total accidents. And this is what for year on year, what we can see. So, it is decreasing year on year with respect to last year. Perfect. So, this is our first.
Now, what we will do, we will just duplicate this sheet and we will be using the same formatting. So, I will just duplicate this. And next, what we want to determine is the total casualties. So, I will name it as casualties. Oh, spelling is wrong. Okay, so this is what total casualties. Right now, what we have to do is, we have to write one more calculation. And I will name it as current year casualties. Okay? So, now in this case, we have to determine the current year casualties. So, how can we determine this, right? So, I will write one more formula for this. IF year of accident date is same formula we will be using. IF year of accident date is equal to current year. Okay? That is parameter. Then we want. Okay? So, from the casualties will be taken from this particular field that is number of casualties. I will just bring it here or you can type it. So, if this particular current year is equal to accident, then we want an output for that particular year selected as number of casualties. And we will end this. Okay? And we are not using any aggregation here. If you want, you can. So, let's say we will be using sum here because we want the sum of casualties. And I'll just close this. And I will click on OK. And if you bring this, and I will replace this particular by current year. So, you can see these are the current year casualties. I will change this and I will name it as total casualties. Play. Okay? So, we want to find year on year increase as well. The next thing what we have to determine is the year on year casualties increase or decrease. That is year on year growth for casualties. So, I will just use the same calculations which we have here. Why we are using the same? Because whenever we are duplicating this, the number of formatting which we have used, right? That is increase or decrease, it will be same, right? So, you don't have to change it again and again. That particular. So, you just have to duplicate this. If you are not duplicating, then you have to go ahead and change your number format again. Perfect. So, we'll just edit this and we will just delete and we will name it as year on year casualties. So, instead of current casualties, we will name it as current year casualties. Similarly, here as well, current year casualties, previous year casualties. Similarly, here as well. And we just click on OK. And we will take this input over here. You can see the value has been changed to 11.8189. So, first, what was that? Initially, if you can see, uh, so right now, if I change this to 2021, so it was 11.70 for this current year, that is for total accident with respect to 2022 and 2021. Similarly, again, casualties for 2022 and 2021 is 11.89. Whenever you are changing this, it will change, right? You can see percentage has been changed. But current year, if you are clicking as same, this is the value for current year. Whenever I'm changing the current year, the current year value will be changed. So, now see, if current year and previous year is same, you will get a zero percent of year-on-year increase. Why? Because current year in previous year, you are choosing it is same. So, growth is not there because in as per our formula, everything will be zero. Perfect. So, if you are changing, let's say you want to compare it to 2019, you can see the value will be changing. Similarly, parameter, whichever parameter we are using here, a parameter use universal for entire workbook, right? If that particular parameter used in any of the calculations, always remember, if you are changing the value on this sheet for this parameter, same parameter values will be applied to the second sheet because it is a universal field. Parameter is a universal field which should be applied to entire workbook. This is an interview question which will be asked, right? So, when I will again choose current year as 2022 and previous year as 2021, similarly it will be changed over here as well. You can see 2022 and 2021 because it is not a sheet level, it has it's at workbook level. Perfect, right? This was the total casualties.
Next, we have to determine the fatal casualties. So, just duplicate this and we will rename this as fatal casualties. Fatal means a worst situation, like there was whatever casualties were there in that accident, and those are very serious or highly serious, right? Right now, the next one is what fatal casualties. And in this, the fatal particular type of severity, accident severity will be coming from this particular field. You can see here, for this field, we have three types of severity: that is fatal, serious, and slight. Okay? And from here, we will be using this particular, what we can say, or we will be determining our values. And I will take a new calculation field for this and I will name it as current year fatal casualties. Okay? And for this, I will write a calculation as we want the sum of this as well. So, IF accident severity. Okay? So, right now, we want because the accident severity have three values. Now, if it is equal to fatal, right? If it is equal to fatal, always remember, whatever spelling or the string which you are writing here, it should be it, it should be of same spelling and same, whatever upper case, lower case, whatever case is there, it should be same, whatever it is there in this particular field, right? So, it is like, first letter is capital, and all others are in lower case, right? If it is equal to fatal, and we want one more condition over here, that is, IF accident year, that is year of accident date, it is equal to current year, then we want number of casualties. Because we are finding the fatal casualties. And we will end this and we will close the parenthesis for sum. Okay? So, we have an error here. So, one more parenthesis is required, that is here. So, I will just click on OK. So, instead of this current year, I will take this and I will just replace it over. You can see, you can just place it over here. Okay? If you cannot do that, you can remove everything and you can just add one by one here. So, now we can see the current year fatal casualties are 2855 for current year 2022. And we will change the name here, that is fatal casualties. We want it as fatal casualties. Play. Okay? So, we want to find year-on-year increase as well. The next thing what we will do, uh, we have to first find out the previous year fatal casualties. So, I will just right-click and I will duplicate this and we can edit this and instead of year, we will change the name as previous year. And instead of current year, we will be using here as previous year. Perfect. Click on OK. And now we will find year-on-year fatal casualty. So, I will just duplicate this. So, whatever the, whenever you are duplicating this, whatever default properties which we have set, that is lower arrow and upper arrow, it will be same for this, right? So, you don't have to change it again and again, that particular. So, you just have to duplicate this. If you are not duplicating, then you have to go ahead and change your number format again. Perfect. So, we'll just edit this and we will just delete and we will name it as year on year fatal casualties. So, instead of current casualties, we will name it as current or fatal casualties. Similarly, here as well, current year fatal, previous year fatal. Here as well. Previous fatal. Perfect. So, just click on OK. And we will take this and we will replace this particular thing. So, you can see it is 26 percent. So, the value has been decreased 26 percent as compared to the last year, that is previous year 2021. So, it is a good thing if it is, if it is the accident casualties, number of accidents, if they are decreasing, it is a good thing, right? If it is increasing, then it's a thing like we should worry, right?
The next one we have determined the fatal, fatal casualties. Next, we have to determine the, so serious casualties of it. So, just duplicate this and we will rename this as serious casualties. Just look at this, same thing again, we have to repeat. I will just duplicate this calculation for us for current year and I will just edit this and instead of fatal, we will be using here as serious. Okay? And instead of fatal, we will change here as serious. Okay? Perfect. All other things for current year will be same. The logic will be same. We will just click on OK. And we will take this and we will just place it over here. So, the serious casualties which were made were say 27,000 zero four five. So, always remember, the spell mistake should not be done. Whatever the name we have, same should be entered in our calculations, like serious spelling, same, whatever uppercase, lowercase, same should be there in our calculations. Next one is slight. You can record your whatever the name which we have, you can write it on a paper or you can copy it from here as well. So, next one is slight. But before that, we have to find out the year on year increase as well. So, now we will change the name as well. Instead of fatal, we will name it as serious. And we'll just click on OK. And we have to find out the previous year serious casualties. So, I will just duplicate these and we will edit this and we will name it as previous year. Let me just, we'll delete this. And here I will change this to previous year. We'll just click on OK. And after that, we have to determine the, I will just duplicate this. And we will find out the year on year. And we will change this to serious. Perfect. So, instead of fatal, we will change here as serious. All other calculation will remain same. Just click on OK. Take this and place it on this. So, you can see it has changed to 16.30. Next and last, we will just duplicate this and we will find it for slight casualties for. Perfect. All right, so we will just change this and we will name it as slight. Just click on OK. And next thing what we will do, we will take this fatal and we will duplicate this for current year. Edit this. Change this name to slight. Delete this. And we will name this as instead of fatal, we will choose here as slight. Okay? Make sure no spell mistakes are there. We'll just click on OK. Take this current year slight and place it over here. You can see slight casualties, the number is high. There are not so many fatal or serious casualties were done during a road accident. So, this is a good thing. But we still have to reduce this as well, right? So, that care should be taken with respect to that. And we should find out the previous year as well. So, we will just edit this and we will oops, we have to first duplicate this. Duplicate. And then we will edit this and we will mention this as previous year. And instead of current year, we will be using here as previous year. And we will just click on OK. And so for year on year, we will just take this and we will duplicate this. And we will edit this. And instead of serious, we will be using here slight. So, change the calculation also. We should use all the slight, current year, and previous year calculations for casualties. Just click on OK. Take this and place it on this. Okay? So, you can see it is 10.82 percent with respect to this filters or parameters, right? So, we have designed all our KPIs. Next, we have to design is our sparklines. Okay? So, if you design once, one sparkline, and everything will be similar for the next one. Perfect.
To develop the sparkline, we will take a new sheet and I will rename it as first. We have to find out the accident sparkline for it. And the sparkline which we are using here, it is a monthly trend which we will be showing for current year and previous year. So, in this case, the previous year will be our area chart. And the current year numbers or whatever we will be showing the current year monthly trend, it should be shown in the line chart. All right, so now for that, to show monthly trend, obviously we will need one date field. So, I will take this particular date field and I will put it into the columns. Okay? So, I will just have put it into columns. And I will right-click and I will choose month as our date part. All right? And if you are choosing, this is the discrete month which I have chosen. And you will be seeing a values from January to December. And instead of standard view, I will just choose it as entire view. All right? The next thing what we have to do is, we have to take the current year accidents and you have to put it into the rows. You have, you can see you have a trend of, uh, the accident month-wise, right? So, if you can see in January, there were around 9967 accidents. And in December, so it's around 9625. All right? Now, next what we have to do is, we have to take previous year accident. And I will put again this into the rows. You can see there are two sections of visualizations have been divided. One is for current year, and this is for, uh, previous year. The next thing we will be making this as a dual axis. So, I will just click on previous year accidents fill and I will just make it as dual axis. All right? So, before doing anything, I always remember to synchronize your axis because the axis which we are showing in a dual axis chart, they should be starting from, or it should start from the same value. And in that way, same value, you can see it's starting for previous year, it's starting from 0 to 16. And for current, it's from 0 to 14. So, we have to synchronize this. So, first, I will right-click here and I will click on synchronize axis. Right? So, both the axis are now having same values and they are sharing same axis. I will just right-click here and I will click on show header. So, I will uncheck this. And you can see this has been hidden. And this for the same, we will hide this as well. The next thing what we have to do, I will just hide this as well. We don't want this. So, and right now, what we have, like, we will be showing previous year in as area chart. So, I will click on previous year for accident fill and I will just make it as area chart. So, you can see this has been area chart. And you can see, if you can see, whenever I'm clicking on this, this particular, when I'm trying to sync the values of current year, which is a line chart, you can see I am not able to see or shoot that values in full tip as well. And it's not selecting, it's only selecting, you can see only one point is selected.
That is for previous year, so what is happening is that, uh, this particular line chart is behind this particular area chart. So we want it to be in front of this area code. So what we will do, we will just swap this pill. So I will just take current year and I will swap this and I will put it into the right side of previous year. Perfect. So you can see this particular line is now highlighting and you can see we can choose this particular and we can go ahead and see this particular values and you can see it is now selected. Perfect.
The next thing what we will do, we will change the colors of this. I will just take this here and I will just double click on this and we will change the colors. So for current year, I will be using this color. So just select this and use this color. And for previous year, we will be using this color. So I'll just click on apply and click on OK. So you can see, uh, this is our previous year that is area chart and this is our current year that is forward link chart.
The next thing what we will be doing here is you have to click on all marks card and we will change the tooltip. So always remember you have to format your tooltip. Always tooltip plays a very important role and it should look, uh, very much, but we can say visually appealing and it should relate very much high amount of information, right? Because whenever you are hovering over anything onto that particular part of your visualization, it will give you some information. So always make your tools better. So I will just convert it into medium and I will make it as a nine. Now just click on OK.
So now we can see full tip is showing a good information that is this is, uh, what is the month of accident, it is January and it is showing a previous year accident as this value. Whenever I'm clicking on this, it will show us the current year accidents, right? Because we are not showing any labels here, so it will tip plays an important role here. All right.
The next thing what I will do, I will just change the labels which we have here. So I will just right click here and I will click on format and instead of, uh, full name, I will just use the first link. Okay. So we will be using only first letter. So that is January, February, March to December. Perfect. So one is done.
The next things are very much easy to do. So we will just right click and we will duplicate this and we will name it as instead of accident, we will name it as casualties sparkline and this will be for total. Right? So now instead of accidents number, we will be using here current year casualties. I will just please take this and I will put it on to our current year casualties. Right? So I will just whenever I'm dropping it on this, you can see the values will change. Similarly, instead of previous accidents, I will take previous year casualties and I will place it over here. Right?
And next thing you have to always see if this synchronize axis tick mark is there or not. It should be always ticked. If it is not, you have to select the time make it or you have to activate that, what we can say, synchronize access button. Right?
The next thing what we will do, I will just unclick headers here, here as well. All other things, you can see the tooltip will also be off video because we are taking that particular duplication of that vertical. So you can see previous year accidents and casualties, uh, we have to change the tooltip here, guys. Okay. So instead of previous or accidents, we'll be using here casualties. So current year casualties and previous casualties, we'll just click here. I will just copy this, paste it over here so that our formatting will be and I will name it as current year casualties. Just Ctrl C, Ctrl V and here we will name it as if you guys here. We'll just click on OK. So now you can see casualties are there for current year and for previous year. Perfect. So this is our current year and previous year casualties.
So for the next chart, what I will do, I will not design the, what we can say, the tooltip. I have shown you how to design the tooltip. You have to go in all marks and you have to delete the first whatever we have, we have copied it from first sheet and you have to add our current years, whatever the sheet you are making of that particular sparkling. Then next way I will do is just I will duplicate this again and instead of total casualties, we want it for fatal casualties. This and instead of previous year fatal casualties, I will take previous, uh, sorry, previously total casualties, I will take previous or fatal casualties and I will just take it and drop it over here. So it will be replaced. Similarly for this as well, for Frontier casualties, we will replace it by current year vital casualties. I will take it and drop it over here and check again if it is synchronized. Yes, it is synchronized. So we'll just uncheck this headers. Perfect.
So again, you have to go ahead and change your, uh, you have to go in all marks card and you have to change your full tip over here. Right? So instead of this particular thing, you have to take your current year fatal casualties and should not be in red, it should be black. Similarly here as well, delete this and add your previous air Kettle casualties and it should be black. And instead of current year, we have to mention it as fatal over here. Similarly here as well, click on OK. You can see your tooltip has been correctly showing. Right?
In the next, you will just duplicate this again and I will name it as serious. So we will replace our pills for previous air fatal, we will replace it by previous year series casualties and for current year, we'll replace it by this. Check whether it is synchronized. Yes, sure. And check headers. Again, I will not show you how to design the tooltip again. You can do it later or you can do it on your own. I will just skip this part and I will just duplicate this and instead of series, I will make it as slide. Okay. So instead of this, we will replace this and for this, we will use this. It's synchronized. Show and check headers. Perfect. And we will save this because I will save it later. All right.
So we have created our sparklines as well and what we will do into our next video, I will show you how to bring this background into this particular Tableau dashboard, uh, and we will start placing our whatever we have designed our KPIs into our dashboard as well as, uh, in next video, we will place this particular things into our dashboard and next we will start designing our remaining charts and remaining KPIs. After creating all the KPIs and all the sparklines related to those KPIs, we will start placing this into our dashboard. So for that, I will click on this particular second icon which we have, a new dashboard and I will click here and you can see the new dashboard has been opened. So I will use the custom size for my dashboard and you can use your phone size whichever you want as per your screen resolution, but I recommend you to use this one. So I will Renee name this as the width for me will be 16000 and I will choose the height as 9900. Okay. So this is the pixel size for me.
And the next thing is like, if you can see in my already built dashboard, I have used the background image here. So this image or the layout here, it is a custom layout which has been built in PowerPoint. So we will be using the same background image and if you can see in my PowerPoint, I have already built this and so I have created a video how to build this particular backgrounds and I will mention the link for you to see and, uh, you can step by step follow, uh, the same thing and you can prepare, uh, the backgrounds in such a way. So I have not prepared the same exact background, but I have prepared it for another dashboard which is the HR dashboard. You might have seen that if you haven't, I will mention the link for that as well. And in that video, I have shown, uh, the complete tutorial on how to make a background for your desktop, sorry, for your dashboard. So this here you can see I have chosen this layout. This is prepared completely in a PowerPoint and for like for your, uh, you know, for variety, I have added some more backgrounds for as well. So this is the first which we will be using in our video. So this is one more combination which is gold and black combination. So this is one purple and black combination. This is an a dark W or navy blue combination as well. Right? So you can use any of this if you want or you can use the same one which I have used.
The next thing what you have to do is after, uh, so I have, I will mention, uh, the link for this PPT as well for you to download. And next thing you have to go in file, then you have to click here on save as. So once you click on save as, you have to choose a folder where you want to save it and next save as type, that is extension, you have to choose it as JPG file. Right? So you can see a JPG file. So this is nothing but you will be saving this particular dashboard in the form of image. So when I click on it, it will ask us to if you want all slide to be saved as an image or you want only this particular slide. So I will just click on this one. But as when I click or when I hit this button, it will be saved as an image format. But I have already created this particular image and I have saved it. So I will not create the Bell. So you can follow these steps and I will show you the one which I have, you know, created or saved or not this one, this, okay. So you can see this will be your saved image. If you, if you see, uh, when you open it in the form of PowerPoint or any image viewer software, uh, you can see this type of background has been saved from that particular PPT. And now this we will be, uh, importing into our tablet export.
So I will open our dashboard and what we will do, I will take one horizon vertical container and I will just place it over here. Okay. So this is the vertical container which has been placed. And next in that particular ventricle container, I will take image type and I will place it over here. Okay. So this is what image object I will take and I will just drag and release it in that particular container. And you have to say fit Image, Center image and I will choose this particular image. So whichever image this name file you have saved, you have to click choose this particular image and I have to click on open and OK. So you can see it has been a perfect fit into our dashboard which we have to size which we taken. So this is the PowerPoint size only. So it looks good. You can take any other size as well, pixel size and it will be perfectly fit into that particular Sizer. So right. So this, this was for us. Now we have bought our background into our dashboard. So this is how it looks.
The next thing what we have to do is we will start bringing our key apis over here. Right? So now to bring a KPI, instead of file, I will make it as now floating first. So you have to follow how I'm doing. First, I will choose this as floating, that is container type. First, I will choose as floating, then I will take a one vertical container and I will try to place it over here. Okay. So this is a vertical container. Then I will go in layout and in layout, I will choose its size. I will choose the width for it as 410 and I will choose the height for it as one one zero, that is one one ten. So this is the container which we have taken and I will try to place it exactly center into this particular background image shape. Right? So this is our vertical container.
Next, what we will do, first thing we have to place is our total accident sheet into this particular vertical container. But before placing that, instead of floating, we have to now convert it into child. Okay. Convert this into tile because we have to move these sheets into this particular container which we have placed here. So once you have chosen tile, then I will take this sheet and I will drag and put it into this. So when you take this mouse by dragging onto this container, you can see it is grayed out. It means that it will be putting this or it will be adjusting our sheet into this particular containers. And I will release this. Okay. So it has been like perfect fit into our this particular container. So now next, you can see there are some parameters which is going outside of this background and taking a place, uh, here somewhere and it is disturbing our background. So what we will do, we will select this and close this. Okay. And it is asking us to delete container. Yes, we want to delete this because we don't want anything outside of this particular bank.
Next thing what I will do, the title which I we will be using, I will just go ahead and hide this title. Okay. So we have just hidden the title. Next thing what we will do, we will take our total accidents sparkline as well. So we have this sparkline. Make make it as styled only. So it should be tiled only. So take this sparkline and bring again into this particular, uh, container. So you can see whenever there is a write when you take it to the right, the shoe some portion of that vertical container is grayed out and when we take here, it's it is grayed out. See what does it mean that it will be placing your sheet to the left, to the right, to the top, from the bottom, but we want it to that right. So I will just click on the right and this is grayed out. You can see and I will just release this. Okay. So again, some major names have been can I will either take this and delete the containers. Okay.
The next thing what I will do, I will just hide this title again and here you have adjusting bar for these two sheets. You can see this bar is there. Next, I will just select this particular first sheet and you can see there is one handle is there over here and I will just double click on the center. So what will happen, this entire particular color external first vertical container which we have placed it, it will be selected. And from here, you have to go and you have to select the distribute content event. If you are not finding that, okay, if you can't do that, select this particular sheet, you can select this or this, here, just select this. Okay. So then you have to go into this drop down and you have to click here, select container horizontal. You have to just select this container horizontal and you have to go into this drop down and you have to hit this button that is, select content evenly. What does this does that this two sheet will be taking an equal place, equal space into this particular horizontal container which we have placed initially. Right?
The next thing is remaining is just formatting. So I will just right click here and I will click on format. Then you have to go into this particular shading option and for worksheet, you have to see it as none. Okay. Similarly, you have to go in, you have to select this particular sheet as well. You have to select the worksheet and you have to say it none. It means what you are just removing the background of that particular sheet. Right? Then I will again select this. You can see there are lines are available here, right? So there are some grid lines available. So we will remove everything. First, we will go into this, uh, border section and into sheet, we will remove, so row dividers and we will remove column dividers. Okay. And next is grid lines. So next you have to go into this particular lines, you have to say zero line says None, you have to say access Texas none and you have to go into rows and you have to say grid lines SNL. Okay.
So now you can see our chart is perfectly clean now. So I will just select this and again click here. The next thing is what this particular number should be white in color, right? So because it should be eye-catching and we are making it as white. So I will just select this sheet. I will go it into this sheet and I will click on text. Okay. When I click on text, there is a here you have to click here, then select this particular thing that is API value and make it white. Click on apply. Okay. So on this sheet, because the background of sheet is also white, so you will also, you will not see the number here, but it is actually present. When you click on this, it will, you can see the number is present over here. Then you have to go into our dashboard. You can see this has been converted to White. Similarly, here we will click on format and then you have your, uh, the font for this particular, what we can say, access for the date names or month names. I will just take this color for this and instead of it, I will choose it 9W, choose it as 8. Perfect. And if you want, you can change to this. No, you don't want any shading here. Just clear this first. Choose it as first letter eight and we will choose that this color. Perfect. Right? So in this way, we have placed our first KPI. I hope you understood how to place the first KPI.
For the next KPI is what I will do, I will just fast forward my video because we will be doing the same procedure again. So I will, I will show you once again for this KPI. Another meaning three I will do. So fast forward. So once you have placed is, once you have done this with this KPI or placing this KPI into our sheet or a dashboard, next what you have to do after you doing this, it is filed. Now you have to again convert this to floating. Okay. Make sure you have converted this again to clothing. Take this horizontal container and place it again over here. Okay. Go to layout, change its width 410 and height as printed. Right? And place it at the center of the shape. Right? Then once this is done, again go into our dashboard. So convert this again to file and now bring your KPIs over here. So first we will bring casualties and next what we will do, we will take our casualty sparkling and we'll place it over here. So now we will delete this because we don't want this container. And next, we will just select this particular sheet. We will just double click over here and we will see distribute contents evenly. We will hide the titles and we will format this growing sheet, say none. Similarly here as well, say none. And we will remove the rules as well. I'm going to sheet none. Similarly here as well, go into the lines, we will remove the zero lines, we will remove the axis sticks and we will remove the three lines as well. Fifth and for each other, we will go into this, select this or we will just right click here, format and we will choose it as 8 in this particular. Perfect. So we will make this as well like perfect. So in this way, you have to place your KPIs.
So the next I will just fast forward and you can follow me or you can do it on your own.