Pivot Grouping Timelines

Pivot Grouping Timelines

In this section, we'll take a look at how pivot tables make it really easy for you to work with dates in your data set. So 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 gonna 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 the 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.

Date Grouping

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 to the information group by day, so I'm going to unselect and instead choose months. 

Filtering Dates

All I'm going to need to do now is click, ok. And now all my information is grouped by month. The next issue is going to be filtering the data. What do I mean filtering the data?

Let me just show you what it looks like to filter this information for the month of April and then November. And I'll audibly say what the problem is. One click, two clicks, three clicks, four click, five clicks, seven clicks, eight clicks, nine clicks, ten clicks. It took me 10 clicks to filter for April and then November. I'm going to clear this filter. 

Timeline

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 that 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 unfiltered all the information. And now I have the entire table back. 

Recap

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.