PL-300 – Section 5: Part 1 Level 4: Adding more control to your visualizations Part 2

41. Advanced Filtering

In the previous video, we had a look at basic filtering. In this video, we’re going to look into advance filtering for text, numbers, and dates.

So, advanced filtering for text gives you a few options. These are similar options you may have had in Excel. So, does the string contain or not contain the certain words or letters? Does it start with or does it not start with certain words? So, for instance, we can say, “Does it contain the word, or the letter W, say?” And you can see three of them contain W, some at the start, some at the beginning. Does it begin with the letter W? So, I’ll click on apply filter and you can see we’re down to two. Is it, so I’m going to put, is it West Midlands? Or is it not West Midlands? Is it blank or is it not blank? So, quite useful, for instance, suppose, you have a list of employees names and you wanted to find all of those who had a middle initial or who didn’t have a middle initial.

Now, suppose, you wanted anything, I’ll just get rid of that filter, anything which has the letter W and it’s followed at some point with the letter M. Well this is when we can use wildcard. So, I’m just going to try W, apostrophe M and apply that filter and you can see it works. So, the apostrophe is exactly the same as it is in Excel and in Windows Explorer. We have exactly the same sort of wildcards. The apostrophe at the start is zero to infinity characters. So, if we had W*E, then it would pick up both West Midlands and Wear and Tyne and West Yorkshire because it’s W followed by E with zero to infinity characters afterwards. So, here there are zero characters in between. Similarly, you can have W question mark S. Here the question mark, stands for a single character. So, it doesn’t stand for zero, doesn’t stand for two. It’s strictly standing for one.

Now, if you combine this, so, we can have all of those that contains the word West and all of those that contain the word Midlands. So, needless to say there will be only one, but it could be that we have a bigger data source with the East Midlands for instance. So, just filtering by Midlands would in turn be insufficient.

Alternatively, you could have something that includes West or includes Manchester. So, that would expand the filter to include both West Midlands, West Yorkshire, and Greater Manchester. So, the Or is restrictive, so the And is restrictive, the Or is expansive. So, the And narrows down your filter. The Or includes other options.

Now, you can, of course, use the advanced features, not just in the Visual level filters, but also in the Page level filters. And in the Report level filters.

Now, let’s have a look at examples for dates. So, we don’t have any dates in here. So, I’m going to click on this visualisation and have a look at the date year here. So, we have the basic filtering which gets us to individual years and because we’re looking at years, we’re really looking at numbers.

So, let’s see what the advanced filtering is for numbers and we have probably what you’d expect, is less than, less than or equal to, is greater than, is greater than, or equal to. Is exactly, is not exactly, is blank, and is not blank. So, for instance, suppose I wanted all of the years between 2000 and 2009. Well, I can say, here’s with, there isn’t a between if you notice, so I have to say, is greater than or equal to 2000 and is less than or equal to 2009. So, that gets the equivalent of a between so between 2000 and 2009. So, now you can see it starts at 2000 and goes all the way to 2009.

We also have Is, Is not, Is blank, and Is not blank. So, that’s what numbers are which is really what this is, but what about if we were to put in a date filter. Now, firstly, we have got two Visual level filters already, but we can add a third. So, these are the columns that we set up that are going to add a third filter just by dragging it into that sort of area and it gets added and it’s the same for Page and Reports level filters. You can have multiple filters just by clicking them nearby. So, a date filter, advanced filter. You can show items when the value is exactly, or is after, or is on or after, or is before, or is on or before, or is blank, or is not blank.

Now, there’s just one word of warning with this. Let’s say I wanted everything in the 2000 to 2009 range and this computer is set up in the American date format and you can see that with M/D/Y, month, day, year. So, I’m going to say, it’s got to be between, so it’s got to be on or after the first, the first, 2000, and has to be on or before the 12th and the 31st, 12th month, 31st date, so December 31, 2009 and apply the filter. And the filter appears to work and it does work in this particular instance, but the complication is that we have this date. So, let’s just have a think about some of the dates so, January the first, 2000, That will be covered. January the first, 2001, that will be covered. January the first, 2010, that won’t be covered because that is not on or before the 21st- December the 31st, 2009. December the 31st, 2009, that may or may not be covered and the reason is, suppose, this was say a list of orders and the time and date that the order was received. So let’s say, an order was received January 1st, 2000 at 12 midnight, well that’s fine, that’s on or after the first of January 2000, 12 AM. Suppose, it happened at one, that’s still fine. Two, three, all the other dates are fine. Similarly, at January 1st 2010, midnight would be afterwards, one am would be afterwards.

But let’s consider December 31st, 2009. If it’s 12 midnight, that’s fine, it is on or before December 31st, 2009 and we see it, but what if it wasn’t one am? Well, that is not on or before the 12, December 31st, 2009 at midnight and that would be excluded. So, this is a bit of a danger of setting is on or after and is on or before for dates. Now, what you could do, is, say, make it 11:59 PM, but I wouldn’t even do that. Simply because what if it’s 11:59 and so and so seconds? It will probably be caught by this filter, but I want to be 100 percent sure. So, what I use is before, reset the time, is before January 1st, 2010. So in other words, it goes from January 1st, 2000 to January 1 2010 but don’t include January 1, 2010. So that includes December 31st all the way up to 11:59 PM.

So, just a word of warning when you get close to this end bit, it really, really makes a big difference what you have put. Is it before, it’s on or before, so just make sure you’re not being caught out.

There is one other type of advanced filtering I would say, and it’s called Relative date filtering and that says, “I want to see items which are in the last or in the next 30 days, weeks, calendar weeks, months, years.” So, anything which is in the last 30 days anything which is in the last four weeks, anything which is in the last year and you can include today or not as you see fit. So, I want anything which has been in the last, I’ll say five years and then if you run this next year you’ll find 2014 gets excluded and any new data might get included.

So, that is the advanced filtering for text, numbers, and dates. So, you can say, is it within a range, is it before, is it after, or is it exactly this and with text you can also use the wildcard’s question mark for one character and star or apostrophe for multiple characters.

42. Filter Top N Items

Now, the final piece of filtering I want to concentrate on is one which concentrates on just a few items. For example, I might want just two items from region name. Now, it could be that I can select those particular items. For instance, suppose I wanted a visual filter on Greater Manchester and the West Midlands.

Now, let’s just remind ourselves of the various sales volumes as we have previously seen. So, you’ll see that suppose I wanted the two most significant items, two most significant areas in terms of sales volume. Well, in 2006, that would be Greater Manchester and West Yorkshire. So, I might go in here and go, okay, I want to see just Greater Manchester and West Yorkshire. And that would be sufficient except it’s not always Greater Manchester and West Yorkshire. Here, in 2009, the top two items are Greater Manchester and the West Midlands.

So, then I would have to go back into here and into visual-level filters and say Greater Manchester and West Midlands instead. So, what I’m going to do is, say, I want the top two items. I can also say, I want the bottom two items. Confusingly, you also click on Top N to get the bottom two items as well. But I need to say, well, how do you say what the top and bottom two items are? If you want them alphabetical order, you can say I want the top two items by region name. And that gives me the top two items in reverse alphabetical order. I have to say the bottom two items to get very first ones. So, the reason for this is because if you’re doing comparators, then the letter W is greater than the letter G, the further along in the alphabet, the higher it is. But generally, you wouldn’t filter based on that. You would filter based on, say, the sales volume or the average sales price. So, I’m going to, say, want the top two items based on the sales volume. So, I’m going to get sales volume and drag that instead. And you can see that overall, the biggest sales volume is Greater Manchester and the West Midlands. So, I’m going to click on 2006 in this visualisation and you can see that the greater sales volume in the top two is Greater Manchester and West Yorkshire. So, let’s have a look, see whether that is right, 2006, Greater Manchester and West Yorkshire on the top. Let’s see what happens if I was to click on 2009. Now the top two is not West Yorkshire, but West Midlands. So, you can see that saying, “I want the Top N” allows us not to say, “I just want these two, which are the top overall or the top ones that I decide at the top,” but they allow context to be driven. So, I can say I want it to be the bottom two items based on whatever year or overall that I’m clicking on. So, we’ve got South Yorkshire and Tyne and Wear, but then it becomes Merseyside and Tyne and Wear. So, it is quite useful to be able to say, “I don’t want very determinative items, I want it to be based on the context of other things.”

Now we can only use that in a visual-level filter. I can’t drag sales volume into the paid-level filter and say Top N or the regional-level filter and say Top N. And really the reason for that is that it’s all based on context. But if I was to say I want the entire page to be the top two by sales volume then this particular visualisation would be affected, and then click on that, wouldn’t really affect this one as much, because in terms of the top two, because this will only contain the top two overall. So, it can get a bit circular argument a paid-level filter would affect this visualisation but want this visualisation to affect this visualisation, and it wouldn’t, because it wouldn’t have all of the data.

So, Top N filter’s very useful. Only can be used at an individual visual-level and it just restricts the amount of information so that it’s just highlighting what’s important in terms of the highest number, the lowest number and then leaves the rest for other visualisations if needed.

43. Slicer

Now, there is one of a type of filter and it’s not strictly speaking a filter. It’s a visualisation. And this visualisation is called a Slicer. And what it does is it goes through with slicers data, which affects all visualisations on a particular page or in a particular report.

So, what I’m going to do is, I’m just going to delete a few of these items. And I’m going to insert a slicer in this spike up. So, it’s the one just to left of the table with a little filter symbol, so just click there. And I add what I want to be potentially sliced or filtered. Well, what I’m going to do is I’m going to add the date.

Now, notice the difference between these two at least at the beginning here. Here we have a list of years and the sales volume, here we have absolutely no measures. It is just a list of the field. And this particular list is being shown in a style, which allows for visual manipulation as well as entering the dates.

So, let’s see what happens when I go to the slider and I move this across. You’ll see that all of the visualisations change and you will also see that overall the bottom two also change as well that we set up in the previous video. It used to be just South Yorkshire and Tyne and Wear and now it is Merseyside and Tyne and Wear.

Similarly, I can change the end date and you can see everything changing. We can enter the dates direct. So, we have a calendar picker. Wherever you get to enter dates, you usually have the option of a calendar picker. So, I’m going to say the first of the first 2000 to the December 31st, 2009.

Now, all of these can be shown or not shown, based on the formatting. So, here we have the slid up, so we can turn that off or on. Here we have the date inputs. We can turn those off or on but we can’t change the size and the font colour and the back colour, for instance. We can add a header either or there is a header, but we can change attributes about it including outline. So, whether we’re going to have a box around the title or not, going to add a title to this.

So, filter by date. There we are. And going to middle align it again, centre it. And actually no, I’m going to have it blank with a white background as before. So, this affects everything that is in this particular page. Now, similarly, I could have a filter, or slicer, that is to say, based on something else. So, if I have a slicer, I don’t want to change that to slicer, I need to deselect it first, add slicer, I’m going to have a slicer based on the region name. So, here we have a series of tick boxes as you notice, here I’m just being allowed to select one particular item, or all of the items. You’ll see that I can add a select all. And I can turn off the single select or I can select multiple items. But by default, this particular slicer, which I’ll just blow up a bit, I have to go down to the items and increase the font size. This particular slicer by default for text only allows you to put one particular item. So, if I can do this for individual items, or do it for a range of dates, can I do it for measures as well?

So, let’s put it into a new slicer. The sum of sales volume, and indeed you can see, yes we can, operating, fundamentally like the date, so we have numeric inputs as opposed to the date inputs in the formatting that we had previously. But everything else is the same.

Now there’s just one thing about these particular slicers. I’ve arranged this, so that this is just for 2002 to 2009. Let’s have a look at the matrix. The matrix continues from 1995 to 2016. This slicer is not affecting anything other than this one page. Now, this falling feature I’m about to show you has actually been added moderately recently, certainly not right at the beginning, and allows for slicers to affect a single page or multiple pages. Now, you’d think it would be somewhere individualization. So, you’d be somewhere in the format, or the visual tools. Interesting view and here it says sync slicers. So, that creates a new page, pane, I mean, and here we have, what is this going to actually represent? What is this going to change? Is this going to change the visibility? Or is it going to change the synchronisation of it? Is it going to do? In other words, are you going to see it? And are you going to be able to sync. So, the advantage of being able to see it is if I check the matrix and go to the matrix, you will see that the same photo, the same slicer is being shown. So, it’s not a different slicer, it’s not two independent slicers. If I change this to 2001, you’ll see that the slicer on this page hasn’t changed. So, why is that? Because this slicer whilst affecting this page is not connected to the rest of slicers. So, if you want this slicer to be the same as this slicer, then you need to check this box here for the page that it’s on, and sync.

So, now, let’s change this, so it’s 2002. And you’ll see that this slicer now is 2002. So, it’s a very odd quirk, that when you introduce a slicer, it is visible on the page. But it will only by default sync to a particular page and not when you have the same two additional pages will it be the same slicer. It’s very confusing. So, if I were you when I insert a new slicer, so I’ll just insert another date slicer just here. I’ll go to sync slicers. The first thing I would do is check this box, which is this particular page, the page has its own and whether it’s syncing, because the rest of it doesn’t really make sense.

Now you can have a slicer filtering on the page. So, in other words, it will do the slicing, but you can’t see it. So, suppose, this slicer filters, this bar chart. So, I’ll just go back up, so you can see that this bar chart goes from 1995 all the way to 2015. So, if this slicer affects the bar chart, it can do that. So, I will tear that to 2000, we’ll now see that they start at 2000 but you can’t see the slicer. So, something on page one affects the bar chart, and you can’t see what it is. So, this causes a problem of course. I’m just here, I’m looking at this bar chart, I see that it is filtered because I know my data range starts at 1995. Okay, why is it being filtered? Okay, I’ll go into my visualisations, I will go to my filtering, and there’s no photos whatsoever, what’s going wrong? Help! So, with that, I then have to look at the remaining of the pages and see if there is a slicer, which is synchronising to that particular page. So, it might be you want a slicer that synchronises to all pages. Great. So, just check, check, check. Job’s a good one, I’ll just insert a new page. Go back to the slicer and just see it’s not selected. So, any new pages you add, you have to go back to the initial slicer, and go check. If you want that slicer to be synchronised to this new page. So, I’ll just get rid of that page by clicking on the X next to it.

So, slicers, a very simple tool, but something that can have big impact across not just this one page, but across all the pages. And the key thing to remember once you’ve inserted the slicers to go to View, sync slicer it’s a bit out of the way, so you have to remember that it is there.