Hello, everybody. Welcome back to dashboard training. In this video, we will see how to create a second pivot using a trend line. The current pivot that we created in the earlier video was relevant to the comparison. Now the next video will be a timeframe where we'll be presenting time as our data. And let's see how to do that.

Now here number suggests we need to create a new pivot entirely. Now that we've already created one pivot, so we'll be using that once again. So to do so, to create a copy of the existing sheet, hold on to the Ctrl key from the keyboard. Place your mouse on the sheet name, click and pull it out. As we can see, there's a small plus sign appearing just next to the sheet that we pulled out. That's an indicator, a copy will be created wherever the small triangle appears.

Now I want that to be on the right side. So we'll just drop it Once done, release the Ctrl key. That's the fastest way to create a copy of the active sheet. It's not just running For the pivot that can be done as well on raw data. So I'll just name the sheet pivot to. And once done, let's work with the data.

Now here I wish to see yearly quarterly reports, that is the count of people getting employed for the yearly quarter. To do so, I require the numbers I do not require the average, simply right click inside of at the place of average and under summarize the value by I'll be choosing count, and that will give me the count. But as we can see, it's two decimals and we we do not require two decimals. So from the Home tab, make it general and then under formatting. Now let's convert this data into a year earlier report. I'll just remove the frames to remove anything from the parameter just keep the cursor in the pivot from the column and the row label just pull the category onto the screen and that will be intern remove the fields from the pivot.

Now I've done a small modification in the data I've added DOJ, that is the date of joining in the field. Now here, there might be two ways to get things done one prepare the data in Excel itself. So let's prepare the data in Excel like yours, it would be, I would like to have yours in my worksheet which can be extracted from DOJ. So I'll just pull in a new heading called yours. And I'll be using a formula yours. Now let's select the parameter that is DOJ, close the bracket, enter, and there it is, I have the DOJ year extracted now we can also use text for that matter, I'll do the same as well so equals to text and select the date of joining but this time only the difference is that we'll be using four times why it can be done for the month.

Could also be done for the days. So there it is. So I'll just keep it yours. Now, I also wish to see days, like which day most of the employment happens like Sunday or Monday or Tuesday, which day of the week do employees mostly join the company. So let's see this. So I'll just say weekdays.

Now this time, I'll be using the formula text. We'll be going with three DS, so I get the shorter name. So there it is. Now this data has been updated to the data, and that is the dynamic data. Okay. Now it should get reflected as we saw in the earlier videos.

Let's try them all. The shortcut again will be alt f5. To refresh the pivot, as we can see, as soon as I press Alt F one automatically two new fields appear To the panel, that is pivot table fields. This that is years and weeks Isn't that fascinating. And the best part is It also appears in the second pivot table as well. Once one of the pivots is getting updated and refreshed, it indirectly refreshes the others as well.

So now, if I update any of the data, automatically, the relevant pivot tables also get updated because all the pivots are connected in the same master file. Okay, so maybe you're wondering what it is that I'm trying to convey. But let's see the data output. Now if I put in yours into the roll label, because there are a large number of years and the weeks into the column labels, and I have the yearly week wise report, but that's not what I'm looking for. I'm looking for a yearly quarter reports. I'll move the week days for now.

And I wish to have quarters to no quarter is something I don't have currently in my data. So I I'll be using a tool which is provided by the pivot table itself condition. There should not be any blank as a parameter into the pivot. And if it is, then it has to be done manually using a formula. Now let's try it and see how to get it done through pivot. I'll just remove the layers or the years for now.

And I'll drag DOJ into the access field that is the row label. And I'm using 2016 it automatically merges the data for me. But if you're using 2010 or an older version, it will not happen. So let me show you how it is done. How to group these dates into a desired output. Now this can be done only on dates and numbers the grouping.

So to group the date first, a date has to be put into the row or column label. Right click on any one of the dates and the right click option. There's a group feature provided, so click on it. A panel appears now the group panel currently that we see is four dates because the data that is provided are dates, there are two features available. One is the start date and the end date which is captured directly from the system, which is the smallest date that is the starting date and which is the largest date is, which is the end date, we can alter this date But do remember there will always be less than the particular date or greater than the particular date at the bottom. Now, if in case we alter the data, we now we wish to have quarters.

So I'll select the quarter. Do you remember the panel that is provided over here does have a multi selection feature, which means I can click in number of times, and the data gets continuously added up to the selection. Now if I want them, just click on them once again, and that will be select. Currently, I have a year field already in my data. So I'll just go with quarters. Do remember if you wish to you can actually have the quarters months multiple data as well.

Let me get back into the normal. So I'll just pull in the quarters and there it is the quarterly Reports are just drag the years about the roll label. And we have yearly quarterly reports. Now to make it even better, I'll make it into a tabular format. So I'll drag DOJ that's quarter into the column labels. And there it is.

Now to change the chart type, simply select anywhere on the chart, which is blank. And right click on the same area now did not select anywhere inside the frame, change the chart type on the line, we can select marker line and that will give us an overall view of how the data looks as the initial years in 2014, for the first, second and third quarter, there was no employment done. That's why it starts from zero. And same goes for the end as well. So let's arrange it over here. So that should do it.

And there it is. I just changed the number over here. That is count of salary will be converted into number of employees and that should do it now. So that's it. Second pivot table which we will be used to create our dashboard. Now in the next video we will see how to create the third and final pivot table which will be used for our dashboard that is called slab reporting.

Till then keep practicing to in the next video. Thank you