Welcome to Highline BI348 class video number 16.
If you want to download this file, BI348Chapter02Start,
or the finished file, click on the link below the video.
Hey, this video and the next three videos
we're going to be talking about frequency distributions
and histograms for continuous quantitative data.
Now, this is our data set we used last video
for categorical data.
We made frequency distribution and column chart for website.
But here we have a date, time for each transaction,
and our first histogram in this video,
we want to count how many transactions happened per hour.
Next couple videos, we'll actually from this data set
calculate an extra revenue column
and then do histograms for revenue.
Now, I want to go over to the sheet
goal for quantitative frequency distribution,
and here's our end result. We need
to get hours for our criteria, and actually, we'll
do this with the pivot table.
Pivot table will make this easy for grouping,
because in essence, for this first category,
we are counting with a lower and upper limit.
12:00 AM all the way up to 1:00 AM, but not including 1:00 AM.
There were 219 transactions.
So we want to create this frequency distribution
for counting by hour and then visualize it
with this histogram.
Now one of the main points of a histogram
is to see the shape of the data.
We can see there's definitely some peaks and some valleys.
Now I want to go back over to the sheet sales data.
Now, we click in a single cell, and we're
going to do this with a pivot table,
so Alt and v. It's asking us where our data is.
Of course it got it right.
I'm definitely going to put it on a new sheet.
There's the OK button.
So I simply hit Enter, and that was Alt and v Enter.
Now I double click this and call this time histogram and enter.
Now I'm going to pull the field list over,
so it's a little bit closer.
I'm going to drag the date and time down to rows,
and then come over here, and it's
going to be smart enough when I right click Group,
it's going to give me a few options.
Now earlier, in an earlier video, we did months,
but I'm going to uncheck that and simply say hours.
And it will know from that date and time data
to roll it up into hours.
So there we are.
Now I'm simply going to drag any one of these fields,
because it's really just counting transactions
for a particular time period.
I'm going to drag Product, for example.
And just to show you, it really doesn't
matter which one you drag.
You're always going to get the same home.
Now, just the base of that grouping by time and frequency
count, nothing can beat a pivot table for speed.
Now before we make our histogram,
let's change the labels.
And remember most the time we go to design report layout,
but I actually don't want the field name here
because it'll say date, time.
So I'm going to take time, and I'm
going to indicate that the lower limit, because this category is
greater than or equal to 12:00 AM and less than 1:00 AM.
So I'm just going to say, lower limit, change the column
width, frequency and enter.
Now we can make a histogram, insert.
Notice just one cell in the pivot table,
it'll know what to do.
And we want column 2D.
Now this is made from a pivot table.
So it has these field labels here, which are nice,
we can filter, but we don't want to filter.
So I'm going to right click and say hide all field buttons
on chart.
Come over here, this looks like chart junk, so delete.
Click on the columns and Ctrl-1.
Now our task pane is way over to the side,
so I'm going to click and drag it over here
right next to the chart.
Gap with 0.
I like color, so I'm going to come over and change the fill
to vary color by point.
Now let's come up to this green plus,
and I'm going to say, give me the data labels.
That will put the frequency count at the top.
And we want to change this because it looks like it's not
going to quite fit.
I want to turn it on its side.
So I click on one label so that most of them
are selected, and Ctrl-1 to jump to the right task pane.
And I can see that I need to click there to close that.
And I'm looking down here.
I don't really see anything for alignment.
Now, one of the problems when you're learning 2013 charting
is that you really do have to click through these
to find things.
But it's under Properties, and then
there's alignment, text direction,
I'm simply going to click.
And you can try either one.
I think rotate 270 will work.
Look at that.
I don't think we're going to need this task pane again.
However, it's easy to open with Ctrl-1.
We now have one, two, and one of them
has to go because it's repetition,
which is chart junk.
I'm going to get rid of this one.
And really, this is your choice.
Sometimes if you want it less cluttered, this is beautiful.
Other times, when you want to see the exact frequencies
above, that's kind of nice.
So I'm going to delete that.
Maybe I'll make it a little bit wider.
And then we need a good chart title, so I'm going to click.
And it appears up in the formula bar.
2015 transactional frequency by hour, and enter.
Now, it looks like things are a little bit crowded.
Well, let's first get rid of the horizontal lines,
we don't need those.
Delete.
And then I want to click and try and select the inside chart
area.
And then if I can very carefully point to that dot,
I can click and drag just a little bit.
And there we go.
That is a beautiful histogram.
We can clearly see the shape from midnight to midnight.
Looks like it goes up and down.
Right at lunchtime it seems to be the highest peak.
And then again around 6:00 PM.
And look at this right here.
It looks like we have a lot of activity between 2:00 and 4:00,
5:00 AM in the morning.
Now, one interesting thing about this
is that we might want and then go back to our data set
and see if there's any geographical data about where
sales are coming from.
In our data set we don't have any,
but we might go if this was our business
and try and track that down and see what's
causing that right there.
All right.
So in this video, we saw how to use
a pivot table and the column chart
to create a histogram for continuous quantitative data
where we actually use time and then create our histogram
with a column chart.
Next video, we will go back to our sales data
and create a revenue column and create a frequency
distribution, cumulative frequency distribution
and a histogram.
All right.
We'll see you next video.