Date Analysis with Pivot Tables: Grouping and Timelines

Pivot Tables, Grouping, and Timelines. In this section, we'll look at how Pivot Tables make it really easy for you to work with dates in your dataset. We're going to start by creating a Pivot Table by moving in some fields.

The good news here is you don't have to create the Pivot Table, it's already been created. All I have to do is click within the Pivot Table and then I'll choose the fields that are listed in the exercise. I'm going to bring Date into rows.

I'm going to bring Sales into values, and then I'll take Market and move that into columns. I've just created a Pivot Table that no one is going to be impressed with. What you can see right away is that this is too much information.

I'm looking at sales for every individual day for all the markets. This is not going to give me a nice summary or overall view of trends as far as it relates to sales. So someone might look at this and say the issue with this Pivot Table is that there's too much information for us to get a sense of it.

Could you please group all of the sales into months? Now if I go back to the original table and take a look, I will see I do not have any month columns here. The best I could hope for, I see, is a day of the week. Perhaps maybe I should create a new column and come up with some kind of function that will convert individual dates to months.

Well, that's not going to be necessary. Within the Pivot Table itself, there is a feature that allows you to group dates by different time periods. All I need to do is right-click on any individual date and choose Group, and currently what I will see here is confirmation that all of my information is grouped by days.

---

Now I don't want the information grouped by days, so I'm going to deselect it and instead choose months. All I'm going to need to do now is click OK, and now all my information is grouped by month. Now the next issue is going to be filtering the data.

What do I mean filtering the data?" Well, let me just show you what it looks like to filter this information for the month of April and then November, and I'll verbally explain what the problem is. One click, two clicks, three clicks, four clicks, five clicks, seven clicks, eight clicks, nine clicks, ten clicks. It took me ten clicks to filter for April and then November.

I'm going to clear this filter. I want to be able to filter my information much more efficiently, so if I click on the Pivot Table Analyze tab, I'll look at the filter group and I'll see an option called Insert Timeline. Insert Timeline was made specifically for dates.

I'm going to click Insert Timeline. You'll see the only field I am able to select is a field that has dates in it, so it's not going to be too difficult to figure out what to do here. I'll just click the checkbox for Date and then I'll click OK, and now I have this display of all the months and I can simply click on the month that I want.

In this case, let's do the same exercise we did last time. I'm going to click April, that's one click, then I'm going to click November, that's two clicks." I've reduced my ability to filter by April and November from ten clicks down to just two clicks.

---

Now I could also select January, and if I go to the edge and see a double arrow, I can move it over to March. Now I'm looking at three months at a time and that's basically the first quarter. I can then click April and then drag it over to June.

There we go, and then click July and move it over to September. Another option though is to clear the filter and simply just click the dropdown for months and choose quarters. Now this timeline doesn't have to be as wide and I can simply click to view any individual quarter with one click and then I can go back to unfilter all the information and now I have the entire table back.

So, in this section, we looked at how you can group your information for different time periods and also how you can use the Insert Timeline feature to insert a timeline to allow you to filter your information also based on different time periods.