PL-300 – Section 4: Part 1 Level 3: Creating different visualizations: Matrices and bar charts Part 3

35. Line and Area Charts, including 8b. Configure Duplicate Pages

In this video, we’re going to have a look at all of the Line and Area charts. So, I’m just going to duplicate this. So, we name this page Line, or Area, and go back up to the top.

So, Line and Area charts follow very similarly to what we’ve just seen in the bar charts. So, first of all, our Standard line charts. So, this is eachQ of the individual areas, not stacked on top of each other. So, you can see Tyne and Wear at 24,000. What’s next, South Yorkshire at 27,000 isn’t being stacked on top of each other. The idea of the Line chart is that it enables you very simply to see where each of these values goes, including if they interact with each other, if they overlap.

Now, this is probably when it’s better not to have similar colours. So, if I switch it to the default, which it already was, but then go into Data colours and reset to default. So, here we can see, for instance, this line goes interweaving. And similarly here, this line goes below the line. So, it allows you to have these individual items on top of each other, if you so wish. I mean, you could always of course, just have one overall item. You don’t actually have to have this subdivision. So, here we just have the total number of sales.

But if you are having many different lines, then you can see the interweaving that comes into play. Now, the next visualisation is an area chart. And it’s moderately successful, and you can see again, the interweaving that happens. And it does allow for you to be able to see the lines as well.

Now, the only problem with this is that each colour is not necessarily associated with a particular region. For example, right here, this darker colour is associated with the second line up, South Yorkshire. But now this darker colour of the area, that is to say, is associated with the second line up, which is Merseyside.

And similarly, this lightest grey is associated with the line second from the top, West Yorkshire, but now it is associated with the West Midlands. But again, it’s the second from the top so it’s the order. So it’s moderately successful. It’s better than the Excel version in that you can actually see the lines even if they are behind of a line.

Now, you can also have stacked lines as well, stacked areas. So, here we can see the 20s, well if I go to left hand side, the 33 on top of the 17, again, is right next to the 50 point of the axis. So, that’s useful if you want to see the grand total as well as the individual build ups. In terms of the formatting, there’s just one new section to have a look at, and that is the shapes or lines section. So, we’ve got the stroke width, how thick you want the lines. You’ve got the line style, so, whether you want solid, or dashed, or dotted.

But really it’s this stepped one, that’s, perhaps, the most interesting. It allows you to radically change what it looks like. And this is the true for line charts as well as for area charts. It looks a bit odd to me, but it could be useful if you’ve got one of these perhaps, being one particular type, and others being another. So, for instance, maybe you have these line charts and you wanted to really show Merseyside as a different type of line. So, I’m going to customise the series and I’m going to say that Merseyside is going to be stepped, and everything else is going to be normal. And this really draws attention to Merseyside.

Not often used, I think, but it’s nice to know that it is there. So, if you want something a bit more striking, you can have this stepped function. And you can customise a series so that just one particular item gets this stepped, or, perhaps, a thicker stroke width, as you wish. All the others are basically the same as what we’ve previously seen.

So, we’ve seen formatting on the X-axis and the Y-axis. We’ve seen formatting of individual data colours and data labels. It’s really these shapes, which is unique to the line and the area charts.

Just for completeness, we’ve also got markers. So, these allow you to have little dots or other types of shapes, and you can also adjust the marker size and the marker colour. Again, you can customise a series. And we’ve also got a zoom slider. So, this allows us to adjust the axis without having to go into the formatting. So, if you wanted to zoom on a particular area, you can do that with the zoom slider.

So, I think most of the unique new functionality for these particular visualisations is in the line section. Used to be called shapes, now called lines, and you can adjust the line style, the stroke-width and whether it is stepped, and also the individual colours.

36. Combo Charts (Line and Column Charts)

Now, suppose, you had more than one measure that you were looking at. So, I’m going to start a new page and I’m going to drag or click on the line and stacked column chart on this visualisation and what I want to do, so, my question is, per year, what is the difference in the 12-month change of flats, also known as apartments, compared to Semi-Detached buildings? So, what I’m going to do is I’m going to drag the date onto the shared axis so this is the axis at the bottom. And we have got a column series.

So, just like the previous series, the legend that we got here, I could drag on region name if I so wished. But I’m just going to leave that blank for the moment. And then it says, well, what are your values? And it gives you two separate values: column values and line values. So what will be on to be represented by column and what do you want to be represented for line? So, I want the flat that’s the apartment 12-month percent change. Here, I represent it as a column and I’m going to change this so that this is the average.

Now, looking at some of these figures, we go all the way up to 27%. It’s possible that house prices went up by 27% but it’d be nice to actually see if it did. So, I’m going to right-click on it and go to see records, which in more recent versions is called show data point as a table. And you can see, yes, it looks like on average house prices did go up by 27%, so this is realistic, and you’ll also notice a few other things that we’ve talked about in previous levels, in previous videos as well.

You’ve also got Include and Exclude. So, if for some reason you didn’t want to see 2003, 2004, then you can exclude it, and it gets removed but I’m just going to undo that.

Now, I want to compare and contrast that with the Semi-Detached 12%, 12-month percentage change. So, I’ll drag that into the line values. So, here, we can see how these two correlate and you can see we got all the way up to 30% almost in 2004. Just make sure that is the average. There we go, that’s better. That wasn’t making much sense. And notice what happened, it was the sum and it was therefore plotting it because it was such a different figure, it was plotting it on a second axis. So, make sure when you drag things in, it is giving you something that… The summation that you actually want. So, here, 2010, we actually have some positive figures plus 3% for some of these attached houses but our flats or apartments are still in negative territory, they’re going down by minus 0.75% a year on average.

Now, we can make this more complicated by putting in the region name. So, if I put region name into the column series then you can see them being built up on top because this is a stacked column chart or on the side because this is a clustered column chart. But notice it only does this for the columns. The lines remain just as the single line.

So, this is a column series, column values and line values. Personally, I wouldn’t recommend doing this too much. It’s starting to get a bit cluttered, but it is informative, it’s telling you at its peak, for instance, Merseyside had, oh my goodness, about 35% change in the flats compared to the overall national change of Semi-Detached of 27%. So, what you can do, of course, is have the same comparator for column values and line values. So, if I drag in, again, the average of the flat 12-month percentage, here, we can see the overall average, and, here, we can see the individual items, the individual regions. So we can see who is above and who is below, in this case, West Midlands, not joining in with the house price inflation at this stage. But then it gets a bit cushioned later on, maybe. As always with these things, make sure that you don’t overdo it, make sure that you are communicating a message to the end user, preferably not too complicated a message.

So, this is how you use line and stacked column charts and line clustered column charts when you’ve got two separate measures that you are going to compare and contrast, or if you’re dividing one measure up into various series. So, let’s have a look at the additional formatting options we’ve got there and we’re going to start with the line section which used to be shape section. And at the bottom, we have got this shade area. So, that turns it from a line and clustered column chart to an area and clustered column chart. Now, you can see there aren’t any options in there so where are the colours? Well, you have to go into colours to be able to change that if you so wish.

So, looking at the other options. So, again, we’ve got the zoom slider that we had before and you can add labels and tooltips and also a secondary Y-axis if you so wish. You do have the option of a Y-axis, a secondary Y-axis so if you use it, then it will be used for the line as opposed to the bars.

Now, do bear in mind, it might not match up so you can see here, the zero on the secondary axis isn’t matching up with the zero on the primary axis. So, do be careful what you do. You can align the zeros if you so wish but now the tops don’t align so it is fraught with some difficulty in explaining exactly what all these different axes are if you’re not careful. Within columns, we only have the one thing which is changing the colours. We have the markers on and off.

Now, we have data labels. So, you can see, gets really confused if you use it with the clustered column chart, probably better on the stacked column chart. Then we’ve got series labels. So if I turn this on, you can see that we’ve got a label for the line. Well, what good is that? Well, if I go back into the add data to your visuals, I can add more than one-line value. So, if I was to add, say the detached 12-month percentage change, then you can see that we have both of those being shown with a label at the end. Now it does get a bit more complicated, especially if you’re using the area option that we had the shade area. So, if you’re doing this, then, my suggestion is to have the bars as simple as possible and don’t use the shade area, it does really get confusing after a little while.

So, these are the additional things that you can do if you are using a line and column chart. And as I say, the biggest new thing is in lines and it’s the shade area and you can change the colour by going to the colours immediately above the shade area option. Finally, if you have problems where the zeros do not align, then you might need to turn the secondary Y-axis and the zoom slider off. So, this is how you can use line and column stacked or clustered charts.