Excel VBA Introduction Part 51.8 - Pivot Table Date Fields and Timelines

welcome to this wise L excel vba tutorial this video is going to explain how to use date fields and timelines in pivot tables we'll start the video with a quick look at creating a pivot table and adding a date field to it so we have something to work with then look at a couple of the basic techniques such as grouping dates by multiple intervals of time and then sorting the dates in different ways as well the biggest part of the video is going to focus on a plain date filters and look at that first by applying filters to the underlying pivot field which is the most powerful way to do it well then we'll also move on and have a look at how you can create timeline slicer which gives you a slightly more visual interesting way to apply simplistic data filters to a pivot table one of the more powerful things about a timeline though is that it can apply to multiple different pivot tables at the same time so the final part of the video is going to explain how you can control multiple pepero tables with the same single timeline so let's get started the starting point for this video is our usual Excel Movies workbook if you don't already have a copy there's a link in the video description you can use to download it and when you do have a copy the first thing we're going to do is write some code to generate a basic pivot table which will include how or at least a two column so let's have straight to the VBA editor to get started with that and we'll have a brand new module then we can write our first subroutine create basic pivot so no big surprises here with what we're about to do we've created pivot tables in several of the previous videos so that's of a quick variable which is going to hold a reference to our pivot cash object and then we'll also have a variable to hold a worksheet which is the worksheet on which our new pivot table will sit and then finally we'll have a PT as pivot table variable which will hold the pivot table itself setting up the pivot cash just as usual we're going to reference the cells on our movies worksheet so I'm going to say set PC equals this workbook dot pivot cassia's dot create now there's a couple of parameters I'm going to fill in here so let's just have a quick look I'm going to set the source type parameter so that that's equal to an Excel database so for an internal exile they do we use Excel database and then finally I'm going to set the source data so I'm gonna refer to the cells on the movies worksheet containing data to make this as dynamic and flexible as possible I'm going to construct the cell references that I'm going to refer to so I'm going to seem WS movies dot name and then concatenate that with an exclamation mark and then concatenate that with a reference to WS movies dot range a 1 dots current region and then finally the address of property of that lot of cells so exactly as we've done in previous videos that will generate our basic pivot cache next we can add in our worksheet and then add a pivot table to it so I can say set WS equals worksheets dot add and then I'm gonna make sure I've got brain j-3 selected on that sheet so I'm gonna say range a three dot select and then I'm also going to make sure that I've renamed the worksheet so it's got a sensible name so when j-3 dot select and then I think WS dot name equals movie pivot it's going to help us later on when we've got a when we need to reference our pivot table and worksheet to have a sensible name so that's why I'm doing that right now creating the pivot table itself not too difficult again we've done it in several previous videos I can say set PT equals PC dot create pivot table and again there are several promises I'm going to fill in and make sure I'll give myself a space underscore so table destination first of all is the compulsory one so table destination is going to be equal to the cell that I've just selected is going to be the active cell the table name I don't need to fill this in but again should help us later on we'll need to reference the pivot table to do things with it I'm gonna say table name colony calls and then again I'm gonna call this one movie pivot I'll spell that properly at some points there we go so there's the pivot table created and added to the worksheet finally we can add a couple of basic fields to the pivot table so let's say PT add fields and I'm going to add in some row fields first of all or a little field and the row fields are going to be equal to the release date column so row fields equals release dates the column feels that I'm going to provide column fields it's going to be simply this certificate column so certificate I'm going to add in a data field as well just so that we've got some data to look at and do this I'm gonna say PT dot out data field as usual and again there's a couple of parameters to fill in the field first of all which is going to be equal to PT pivot fields and then I'm just gonna use this standard one that I've been using for the previous few videos I'm gonna set this one equal to the run time field the function that I'd like to apply is going to be the average function so I'm going to say function colon equals and then Excel consolidation function not Excel average last quick thing to do is just apply a quick bit of basic formatting so I'm going to say PT dot data fields open parenthesis and refer to the first data field which is the one that I've just added then to this number format property to be equal to zero point zero zero okay so at that point I'm just going to run this everything just to generate my basic pivot table on a new worksheet and there it is so what we'll look at now is how we can start modifying this date column grouping filtering and applying time lines the first thing we're going to look at doing to our date field is gripping it so you can do this of course manually by right-clicking on a date cell and then choosing it a group you'll get a dialog box provide me with a range of different options for how you can group your data so it allows you to choose a start date and an end date and then an increment of time or multiple increments of time of all ways you can group these valleys we're going to write some code that will achieve the same results so let's just head back into the VB editor at this point and create a new subroutine that's going to be called grouping dates I'm going to start them by creating a couple of variables that will give me references to both the worksheet and the pivot table I'm working with so I'm going to declare a variable called WS as worksheet and then I'm gonna say dim PT as pivot table and then I'm going to refer to the worksheet that I created in the previous subroutines are gonna say set WS equals worksheets and open some parentheses and I called my worksheet movie pivot this isn't necessary just makes life slightly more convenient when we're manipulating objects on the on the worksheet and the pivot table itself of course so the next thing I'm gonna do is say set PT equals WS dot pivot tables and then again I know that I've only got a single pivot table on the worksheet so I could refer to it as a pivot table as one but I also give it a sensible name earlier on I gave it the same name as the worksheet so just to quickly cheat I'm gonna copy and paste movie pivot from the previous line I'm also gonna declare one final variable and this is just gonna give me a quick way to refer to the first date cell in the in the pivot table so just having a quick look back at the pivot table date range you can see that it currently starts in cell a5 now that's purely because of where I placed the pivot table in the first place had I decided to place my pivot table somewhere else of course the first stage would have a different cell reference so what I'm gonna do to set a reference to the first value in the date column is refer to the row range property of the pivot table so I'm gonna say dim are as range and then once I've set a reference to the pivot table I'm gonna say set R equals PT dot Rho range now the row range really does refer to the entire range of row cell so just to demonstrate that very quickly if I said r dot select and then just executed that subroutine and looked back at the Excel workbook you'll see it started everything from cell a4 so including the row heading and then all the way down to the very very last cell containing a date all the way way down somewhere here at the bottom including the grand total row I don't need to refer to that entire column of cells I just need to refer to the first date value in there so it's essentially the second cell in the row labels range so if I go back to the VB editor rather than setting R to be the row range completely I'm going to set it to be equal to cells 2 comma 1 of the row range so the cells property of course gives you the ability to reference cells by row and column index so row 2 column 1 if I now just run that subroutine again you'll find that when I look back at Excel I've only got the first single date cell selected now I don't technically need to select that cell in order to group the dates all I need to do instead is say r dot group and then there's a few parameters to fill in for this just as you saw in the dialog box that we've just looked at you get the option to choose a start date and an end date and then there was a range of different date periods we could select as well so I'm just going to fill in the start and end properties first of all so I say start colon equals now although I could specify an individual specific date here it makes much more sense to just use the entire date range that my pivot table contains and the simplest way to do that actually is just to miss out the start and end parameters completely you can also set their start and end parameters to true and that will include the full date range of the pivot tables I'm going to do that for this example start and colon equals true the next thing I have to do is set the periods of rich which I want to group so for a date field I can group by different units and time different increments of time now the way this works is a little bit awkward what I have to do is pass an array of seven values true and false boolean values that indicate whether or not I'm grouping by that particular increment of time so to begin with if I wanted to group by just the year what I would have to do is pass an array containing seven different true/false values where one of them is set too it's not very helpful at this point because it didn't we tell you which one you need this attitude but there is a way to find out if you have a very quick look there is a range group method page of course as there is for every method available in in VBA so on the MSDN site there was a little remark at the bottom there that shows you which which value which element in the array refers to which unit of times it's the very last one that refers to the years increment so let's head back to the VB editor and what I can do here is type in exactly kind of annoyingly I can't even spell it probably there we go false I need six falses followed by a single true so I'm just gonna copy and paste as my six forces followed by a true then close the parentheses for the array function so there we go there's my full periods array set so having done that I always to just run this subroutine at this point and switch back to excel you should see that this time rather than showing the individual dates in my list I've got everything grouped by year so it should be a much shorter table than the one that I was showing you earlier on if you can group rows then it stands to reason that you should also be able to ungroup them and indeed that's pretty straightforward to do so I just switched back to the VB editor and type in a simple statement that says are dots ungroup appreciate that this fairly returned into group instead of rows and then immediately ungroup them but just to demonstrate that this is the case if I were to run that subroutine and then switch back to excel you'll see that I can now got individual dates again rather than groups by years you can also group by more than one interval of time so let's say for instance I wanted to group by year and month all I needed to do to make that work is that just the values of the array that I pass in so switch match at the VB editor if and I could if I can identify which of the falses equates to the months interval I can set that to true so again just watching back to the page I showed you on the MSDN site you'll find the months is the fifth element of the array so it's it's just before quarters in the sequence so if I switch back to the VB editor again and change that fifth false to true and then again I'm just going to comment out the r dot ungroup statement just so we can see the end result if I were to run that and have a look back at Excel we'll see grouped by year and month it's possible to sort items in the different groups as well so it might be nice just because of their data that I've got available I've got more data for her for recent years and I have for older years it might be nice to see the more recent years at the top so I could sort by a year in descending order but I want to maintain an ascending order for the months inside each year so to make that work I've got to be able to reference the two columns or the two fields involved in the rows area there so I still got release date available that's displayed there as the the month names and I've got the year's feel available so that's been added in when I've grouped by years and months so to make this work if I switch back to the Beebe editor and then I could just add in a couple of extra lines just below where I've grouped my data I can reference a pivot field in my pivot tables I can say PT . pivot fields and then I can reference first of all let's go for the years column if I refer to the years field first and then I can apply an auto sort method to that now suddenly the intellisense breaks down at this point as you can see I'm not getting any help here whatsoever so if you wanted some help with that what you could do instead is say dim PF as pivot field declare a variable to hold a reference to it get rid of the auto sort at the end there and then I could say set P F equals P T dot pivot fields yes the only advantage this gives me is that I mean if I now say PF dots Auto sort you're the see that you get some intellisense and get some help with the tooltips and gives you an idea about what things you can provide so what I'm going to do first of all is set the order now the order I'd like for the years is in descending order so I can say Excel descending for that and then I've also got to reference the name of the field that I'm applying the sort to even though even though I'm applying it to a pivot field that just referenced as years I've also got to state at the name of the field so in this case the name of the field is yes what a commend you is just copy and paste those two lines to essentially do the same thing for the release date field so if I changed this from years to release date but in this case for release stage which has been displayed as the months still I want to sort that in ascending order so just change their two names the sort order to ascending rather than descending okay so having just a nut low now rim this everything and have a look at the results back in Excel I'll see that I've got more data to look at because my my later years are at the top of the list but I still maintain the ascending chronological order of months within each year another very useful thing you can do with dates is filter them so looking at the drop-down list just at the top of the row labels column if I click on the drop down arrow there of course I've got the standard type of filters or I can just select individual items but it's also possible to use custom dates filters if I hover over the date filters item there's a whole range of interesting things I can do here so let's say for instance I wanted to see just the results for this year I want to see a range or all dates before all dates after I can apply all these sorts of things in VBA so let's switch back to the VB editor and what we'll do is creating new subroutines somewhere down towards the bottom so I'm going to call this one sub filter dates now just as in the previous example it's going to help immensely tap some variables to help us reference the various items so let's just scroll back up to the previous routine and let's just cheat and copy and paste a whole range of variables so we'll get all the variables and the two lines which set references to the worksheet and pivot table objects we can then just head back down and paste those in we're not going to need the range variable in this example so I can remove that but I will set the pivot field variable to reference the specific field in the pivot table called release date so PT dots pivot fields and then opens in parenthesis in quotes and refers to the release in date field try and spell that properly there we go you can now apply filters directly to the field by saying PF dot pivot filters and then use the add or add to methods depending on which version of Excel you're working with so add two in my case I'm in Excel 2013 so there are a few parameters you can fill in here but just one really quick simple one if I wanted to just see all the results for this year I can set the type to be equal to so I'm going to type in the word type so type equals Excel date this year you'll see all the date filters are kind of dripped in a list here so you'll be able to see with fairly central descriptions what each one is gonna do but as I say in this case I wanted to say excel date this year so I'll select the one from the list and then all I've got to do at this point is run the subroutine and if I have a look back at Excel I'll see that I'm now only looking at the data for the year 2016 now if I wanted to apply a different filter to this dates column I'd have to first of all remove the existing filter so let's say for example I want to see all the dates that occurred in the month of June for any year of the entire list if I switch back to the VB editor and I try to apply that immediately so I'm going to say I'm going to change the filter type so it's Excel all dates in period July so that will to choose all the days in July regardless of which year they were in if I attempt to run that at this point it's gonna fail because there's already a filter applied which conflicts with that one so in order to make that one work and after first of all remove the filters or clear filters I can put a filters in a couple of different ways I can clear filters from the entire pivot table which is kind of a catch-all kind of solution so I can say say Pt clear all filters that will remove everything from the entire table I could also choose to remove a specific individual filter from my pivot field so I could say PF dot pivot filters and then refer to the first filter I'd applied and then say dot delete that would delete just a single individual filter alternatively I could just clear all the filters from that single individual pivot to field so I could say PF dot clear all filters and that's the technique I'm gonna go with so I'm going to delete the previous series delete all the other filters and then run this over to you one more time and this time I'll have a look back and I'll see that I've only got July but for every single year in the list you can create more flexible filters by applying specific criteria so let's say for instance I want to see all the films released since the year 2000 so since the first of January 2000 that means I'd have to reference all the dates after the 31st of December 1999 so to make that work what I can do is head back into the VB editor I'm gonna change the type of filter on my playing so it's not Excel all dates in the period you lie is Excel after now it kind of stands to reason that if you're specifying after a date you'll also need to pass in the date you're interested in so I type in a comment and we display the tooltip I can do that by specifying the value one parameter so I say value one colon equals now the simple way to pass dates in is is a string of text but do be careful with this is kind of dependent on the regional settings of your machine so working on a uk-based machine with UK regional settings I'm going to enter my date as 31/12 slash in ninety nine nineteen and ninety nine so if you're in a different region where you do it's in a funny way where you've got the month first followed by the day make sure you do it that way for your particular region anyway having done that if I were to just over run that subroutine at that point and have a quick look back at the VBA editor I'll see that I've got and dates sorted for all the years since the year 2000 so anything before the year 2000 won't be included in the range you can also ask to display dates within a certain range by specifying a start date and an end date so in order to make that work if I switch back to the VP editor of course I'll need to modify their type of filter first of all so I'm going to say rather than Excel after it's gonna be Excel date between let's go for the year let's go for the year 1999 then I guess let's see all the films made in the in 1999 so valley one is gonna have to be the start date of 1999 south zero one zero one and then value to which is another parameter in the add to method I can say value two colon equals then again entering as a string of text I can say 31/12 slash 1999 so having done that if I were to run that subroutine and then have a quick look I'll see that I've got everything in that specific year rather than hard-coding the date range you want is also possible to calculate to the dates which allows you to create slightly more dynamic filters so let's say for instance whenever I built my pivot table I always wanted it to show the last six months worth of results for example so in order to made that work I'd have to calculate the start date and end date by saying the end date would be today's date and the start date would be six months ago from today's date so to make that work if I switch back to the VBA editor just to make this a little bit easier to read I'm going to separate these parameters on two different lines the end date is fairly straightforward you can just use a function which returns today's dates and in VBA the name of that function is simply called date if I wanted to calculate what the date was six months ago from today's date then to do that I can use another function for the value one parameter called date add so I write the dates bad function I've got three parameter to fill in the interval the number of those intervals and the date to which I want to add or subtract that number of intervals now the intervals you pass in here are entered as strings and again there's a help page on the MSDN site that helps to explain what's the various intervals are so I've wanted to go back years I don't for wise everyone to go back months I enter a single M as a string so back in the VBA editor if I type in a single M in double quotes the number that I want to add or subtract in this case is minus 6 and then the dates to which I want to add or subtract that number is today's date so I can use my date function again but just close the parentheses at that point I can now run that subroutine again and have a look back at the results and what I'm looking at here is all the films are all the data for films released in the last six months so there's not much there actually as it turns out there's only born in this particular example so depending on which dates you run this code you may find that you don't return anything at all so it might make more sense just for the sake of demonstration if you switch back to the VBA editor say let's go back a couple of years perhaps so I changed the the increment of two years let's go back two years and then that subroutine again switch back to the VBA editor now I've got the last two years worth of data another way to filter dates in a pivot table is to use something called a timeline now a timeline is just really a fancy type of slicer that works with dates values you can insert them manually if you have a cell in your pivot table selected from the analyse tab in the ribbon you can choose to insert a timeline so just another kind of slicer of course you can do it manually but I'm gonna write some code to do this just to demonstrate how that works if you've followed the video on inserting slicers the previous video that I made then you'll be right at home with our timelines work at least how you set them up in the first place so let's just start by removing all the filters I'll do this manually just very quickly and easily by clicking on the drop down arrow at the top of the row labels box and choose clear filter from years then I'm going to head back to the VB editor and I'm going to create a new sub routine that's going to add in our timeline as a new soap called create timeline just as before we'll find it useful to have a few variables to hold references to object so switching back up to the previous routine I'm gonna copy all the variables I declared plus the set of WS in set PT lines we're gonna paste that back in I'm not gonna need my PF variable so I'm gonna replace it with a couple of extra variables one called SC which holds a reference to something called a slicer cache which is kind of the equivalent of a pivot cache but for a slicer so it's the bit that does the hard work with all the filters the actual visual object theater physical thing that user can interact with is simply called a slicer so I'm going to declare SL as a slicer I've got my references to my worksheet and pivot table so I'm gonna do next is create my new slicer cache by saying set s C equals this workbook dots slicer caches dot add or add two in this case for this particular version of Excel I opened up some parentheses there's a few different parameters to fill in now you might remember from the previous video if you use named parameters for this method when you're adding a slice of cache for some bizarre reason the whole thing falls over so unlike every other example where I've been naming parameters for methods which I always like to do because I think it makes it go much more readable you can't do it for this particular method it just makes the whole thing fall over have no idea why I guess Microsoft called that a feature but there we go so without naming these parameters I'm gonna set the source to be equal to my PT must my pivot table so if I reference a pivot table the slicer cache will actually use the pivot cache of that pivot table to generate the slicer cache the second thing I need to do is set the source field to us fairly nice and easy if I remember to add in the continuation character space underscore so the release date field is the one we're gonna use here of course then we can give the slicer cash and name and that's going to help us again later on we need to reference it again so I'm gonna get my slicer cashier name I'm gonna call it date slicer cash and I can reference that name later and then finally I can change the slicer cache type now there are two possibilities here but look for the Excel slicer cache type in the Immersion there are two options so for a normal slicer of course you'd use excel slicer but for a timeline we'll use excel timeline so I'll select exile timeline and then close the parentheses and that will create my slicer cache one little thing to watch out for when you're adding slicer caches is that if one already exists for the field you've selected then this line of code is gonna fail so just to demonstrate that if I run this one once it works happily if I try to run it again immediately it fails because the slicer casual already exists so it's worthwhile having a quick little bit of error handling to ensure that their slice of cache on that particular field doesn't yet exist so what I'm gonna do is just at the top I'm gonna try to delete the slice of cache first I'm gonna say this workbook dot slicer caches and then because I've referenced it with a specific name I can say date slicer cache and then I can just apply attempt to delete it now of course this will work in this particular case if I run this one this time it will work every single time but if that slicer cache hadn't existed in the first place this line of code would have failed so it's worthwhile having a simple little bit of error handling to say on error resume next which will just ignore the error if one occurs on this line and then after that we can say on error go to zero which just resets all the error handlers we do have a separate video that which goes into much more detail on error handling in VBA this part of this main VBA tutorial series so feel free to have a look at that one if you want a bit more detail and what that does but again if I can run this one again as many times as I like now none of neither of these two things will crash because it deletes the slice cache and then create a new one from scratch so the next thing to do then is actually create the slicer so if I were to set a cell to be equal to SC dot slices not add now there are a few parameters to fill in again then the mains parameters don't work for this particular method so the slicer destination that's fairly simple the slices destination is the worksheet that I've referenced up here earlier on so I can say WS the other parameters I wanted to fill in well first of all I want to I'm gonna skip a little level parameter that's not particularly important for this particular case that's only for cube sources for pivot tables so I can skip a little level parameter by typing in two commas actually after the the WS this is why it's so frustrating not to be able to in the parameters I could have actually named the parameters and specified them exactly rather than typing in these multiple commas anyway the name of my slicer is gonna be called something really simple like date slicer so again I can reference that later on then the caption I can set that to be something like select date range or something along those lines and then I'm not gonna specify the top left with the height properties for the slicer at this point I'm gonna set those separately just in a moment once we've created the actual original slicer so if I can just get you to run that subroutine at that point have a quick look back at the Excel workbook will now see we've got a new slicer with the ability to choose dates it's probably worthwhile mentioning how you can get rid of slices if you want to remove them so one simple way to do that is actually just to delete the slicing cache in the first place if I switch back to the VP editor and then just use the f8 key to step through this subroutine if I step through the part up to where I delete the slice of cache and then just stop running this every teen at that point have a look back at the VBR the Excel workbook you'll see the slicer itself has gone it's also possible to delete this slices individually from the pivot table if I switch back to the VB editor I could create a separate subroutine that will do this so I say delete slices from pivot so to make that work I'll start with a couple of variables again so in fact I'm just gonna copy and paste variables from the previous routine and then I paste those into this new subroutine I can get rid of the slicer cache variable I shall need that one I've got references to the worksheet in the pivot table what I could do then is just a loop over the slices collection at the pivot table so I could say for each SL in PT dots slices and then say next SL and then inside that loop I could simply say SL dot and delete so again if I were to run the original subroutine which will create the slicer or the timeline in the first place there it's it's go back to the VB editor and then the miss subroutine I'll find that the slicer has now disappeared so I do actually want to create to this slicer there so I want to modify something about it so I just click back into there create timeline subroutine and then just run that one one more time to make sure that the slicer does actually exist there are several different things you can do to modify the appearance of the timeline and both with size and position as well so to demonstrate how that works if I switch back into the VB editor let's have a new Sibley team down at the bottom of the module which it can recall something simple like formats timeline and then I'm going to copy and paste the declarations of my variables and the to set statements from the previous routine I'm also going to add in a separate set statement that's going to say set SL equals PT dos slices and then I can refer to my slicer as a date slicer that's the name that I gave it earlier on having referenced that I've got a few different things that I can do so just very quickly I could change the position of the slicer I can set its top and left properties so if I say SL dot top this lets me set to the distance from the top of the screen to the top of or sorry the top of the worksheet to the top of the slicer so I could set it to be in absolute positions like for instance 100 points or I could refer to a range of the pivot table so it's nice to be able to say here PT dot table range 1 I'm gonna mention this in previous videos but table range 1 is a range of the pivot table except for the page fields at the top so I say table range 1 not the top that will position the slicer and time line at the exact same level as the top of the pivot table I could change its left property so that it's just sitting to the right hand side of my pivot table so I could say SL dot left equals and I could set this to be equal to PT dot table range 1 dot left plus PT dot table range 1 dot width plus a tiny little buffer of let's say 20 points or so finally I'm going to change the width of my time line I'm not going to change its height the height is reasonably good already so I'm gonna say SL dot width and make that equal to something like 500 points or so having done that if I would you just run this subroutine and then have a look back at the workbook I'll see that my time line is now moved so it sits up at the top level of the pivot table just to the right of the pivot table and it's a little bit wider so it's slightly easier to use as well the formatting options for timelines are pretty much just as limited as they are for slices so if you remember from the previous video you can apply styles to a slicer or a timeline I switch to the options tab having selected the timeline there's a bunch of basic timeline styles and all they really do is just change the color so you can quickly easily see that they change the colors of the various elements of the slicer is virtually not worthwhile viewing but if I wanted to apply one of these styles in code I could do that so the names of these are kind of almost the same as the names shown on the tool tips if you are the mouse over it they're slightly different so if I just switch back to the VB editor I can say SLE style equals I've got to type in the name of the timeline style but without spaces so is time slicer style so it's not timeline style this time a slicer style then I can apply let's go for dark six for instance then I were to run that subroutine have a look at the results and go this fairly horrible shade of green let's go for a slightly different one if I changed it to style light five and then try that one again have a great look I've got a slightly different style applied one slightly more useful thing you can do with the timeline is change the units of time that it's defaulted to so here I'm looking at the months you increment for this particular time I which means if I click on an individual component there it filters for a single specific month of course it can extend that range across multiple months I want to do by clicking and dragging or you can just remove the filter all together we'll do this in code in just a moment but sometimes it's useful to be able to see different increments of time so with the months option up here I can change it to years quarters or days instead so all that does is changes what will be filtered when I select a different item in the timeline so if I wanted to modify that myself so if I wanted to switch from months to years say let's switch back to the BB editor and as well as saying SLR style I can say SL dots Timeline view state and dot level and I can make that equal to four of the different increments of time as we've just seen there in the drop-down list so it's currently him and so I want to set it to years I can just add that line of code and then run the subroutine again switch back to the two Excel you'll see that is defaulted two years rather than months so it's relatively easy to switch between those two those different increments of time now of course the whole point of having a time line is it allows you or the end user to filter the pivot table in this sort of nice visual way so you can click on individual elements and click and drag to span a range of months or years or whatever range of values you've got selected on there on the timeline what I'd like to do here to show you how you can do that in code so of course you can do all this sort of stuff by just applying filters to the actual pivot table field but you can also do with the slices let's have a look at how you can do that if I switch back to the VB editor let's have a new subroutine which is called something like filter and dates with timeline and what we are going to be doing here is we're going to be using the slicer cache rather than a slicer object itself so I say dim SC as a slicer cache then I can set SC to be equal to this workbook dots slicer caches and because I gave my slice of cash a sensible name earlier on I can reference it as date slicer cache so that will give me a reference to the object and I can I can now start to apply filters to it now the filters you can add using a timeline a somewhat less sophisticated than those you can add just by using the underlying pivot table field but nonetheless is still worth are seeing what you can do so I say SC dot timeline state dot set and filter date range you'll see that they you've got a start date and an end date parameter and that's basically it a timeline the legatus filter for a specific date range but you can't use any of the more sophisticated types like we saw earlier one for the date filters still we can apply a start date and end date by typing in those parameter names I'm going to say starts date column equals not necessary but as I say said before I always like to do this and again the start date and end date can be entered just as literal strings so that's just say for instance 0 1 / 0 1 / 2000 for this example and then I'll set the end date so that that's equal to the last day of that year as well so I'm going to say is 31/12 a slash mm again making sure thats in the particular format filled the region you're in so if I was just to run that subroutine at that point have a look back at the movies workbook you'll see that I'm filtered just for a year 2000 and that's what it's shown selected there if I change the increment of time get there in the in the slicer or the timeline just to show you that it's um that it is the entire range of January through to December boobs got a little bit too far to scroll back quickly there we go so January through to December of the year 2000 if I wanted to clear any filters from my timeline that's pretty straightforward too so if I switch back and I wanted to just clear all the filters from the slice of cash I can say SC not clear all filters just the same sort of technique used for pivot tables early rom so I run that so editing again look back at the V excel I'll see that I've got all the data back okay let's just switch about two years me that simpler to see you can still use the more dynamic technique for setting the date range as we looked at earlier on so just switching back to the VP editor to do that we can set the end date so that's equal to today's date and then I can set the start dates let's go back let's say three years back from today's date so I say date add open some parentheses and say the interval will be in years then the number I want to add is minus three and the date at which I want to add those years is today's date if I just make sure that I've commented out the clear all filters statement and then just run that subroutine again looking back at Excel you'll see that I've indeed got those three years selected again you can see clearly it's actually got this specific date range here December the 7th 2013 to December 7th 2016 so it's not just the entirety of 2014 and just just try to indicate that they're on the slice it's not got the entirety of 2013 selected let's just clear all the filters them from that particular timeline and then let's have a look at one more util interesting thing you do timelines that you can't do with basic filters in a pivot table so what we've really seen for time line so far is they were a slightly more awkward and less powerful way to filter dates in a single pivot table but they do have one trick up their sleeve that you can't apply using basic filters in a single table a time line can be connected to to multiple tables at the same time so for dashboarding type effect could say it's great for being able to control the filters from multiple tables all the ones just like four slices so what we're going to do is write another subroutine quickly that's going to generate a new pivot table that's gonna sit somewhere around cell let's say cell j12 here he's gonna have a slightly different arrangement to this pivot table but it's going to be connected eventually to this same slicer and be filtered at the same time so to do that let's switch back to the VB editor and to start with what we can do is head right back to the very top of this module and just copy the entire subroutine that we created right at the very start to generate a pivot table I'll just paste that in now down in the bottom I can say create new pivot and then we'll need to make a few modifications to the way this code works just to set up the new table I'm just going to modify the code for the worksheet first of all so rather than creating a new sheet I'm going to reference one that already exists so I called the worksheet a movie pivot earlier on so I can reference that existing sheet then I don't want to select arrange a three I said I wanted to select something like j12 I don't then need to change the name of the worksheet so that was can that line can be removed entirely it's already got a name what I can then do is change the way that I'm setting my pivot cash variable so I'm just gonna take them entire section of code out for the moment and then just paste it in down below where at once I've set my worksheet variable I don't want to create a new pivot cash at all what I want to be able to do is set my pivot cash variable to refer to the pivot cash of the existing pivot table on this worksheet it's gonna say WS dot pivot tables and then I gave my pivot table a sensible name earlier on I call the movie pivot and then I can refer to the pivot cash property of that pivots a loss of pivot cash will return the pivot cash on which that table is based as this new pivot table is of course going to be based on exactly the same set of data there's there's no reason whatsoever is create a separate cash for that so I've then got the ability to create a new pivot table using the pivot cash I'm gonna give it a slightly more interesting name I'm gonna collect movie pivot one I'll need to reference that name again shortly and then let's change the fields that we add into the pivot tables let's change it from release date in the road fields box I'm gonna change that so it's the gem genre column in the row fields and we'll stick with certificates in the column fields will stick with run time for the data field with a same number formatting so at that point all I'm gonna do is run that subroutine to create a second pivot table on the same sheet that currently isn't connected to this slicer or so I might you see that if I select items in this time out at this point is not affecting anything on the second pivot table so the next job is to connect this second pivot table into the time line so let's head back to the VB editor to do this and we'll have another new subroutine just to somewhere down below we'll call this one serve connect time line two pivots something along those lines we'll have a few different variables here I'm gonna have a variable for a worksheet a pivot table and a slicer cache so just to quickly copy and paste those three variables from the previous routine I'm gonna change the names of one of them so it's not PC is SC and it's not pivot to cache its slicer cache instead so I've got my worksheet and pivot table variables I'm gonna set my work you can pivot table variables to refer to the same objects I've been referencing previously so scrolling back up to reference my movie pivot objects familiar one I can copy and paste those two lines and rather than referencing the movie pivot I'm gonna reference movie pivot one so the new one that I've just created I can then set my slicer cash to be equal to in this workbook and darts slicer caches and then instant parentheses I've called it date slicer cash I could also reference it as the number one as you see only pivot sorry only slice of cash that I've created in this workbook all I need to do now is say SC dot pivot tables so sighs Akash has a collection of pivot tables which is connected and then I can say dot add and then reference the pivot table that I want to add into it so I can just say PT and I've just referenced it might be worthwhile just very quickly applying a filter to that so again I can just copy and paste from a previous subroutine one which filters it for the previous three years serve a copy and paste that line and then simply run the subroutine one more time have a look back at Excel and you'll see hopefully this time that the second table is now being affected by the slicer or the timeline if I change the timeline it will affect both tables as you can clearly see so that's one nice feature of timelines although they're a little bit more limited in terms of what they can do in terms of date filtering they can be connected to multiple different tables and control them all at the same time so hope you found that video useful thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 13,697

Rating: 5 out of 5

Keywords: excel vba, visual basic for applications, pivot table, date, group date, timeline, slicer, filter, date filter, pivot cache, slicer cache, wise owl

Id: 9LfI7eKJbAI

Length: 44min 1sec (2641 seconds)

Published: Wed Dec 07 2016