Now that Alex has a good idea of the layout and structure of his dashboard,
he's ready to start preparing the data and performing any necessary calculations.
So let's come across and have a look at our data.
This data will be imported every six months.
The first step would generally be to go about cleaning the data,
but we are lucky and then this data is already very
clean and doesn't require further retention.
While it is usually good practice to put calculations on a separate sheet,
sometimes it may be more pragmatic to put a few helper functions in with the data.
And that's what we're going to start with doing first.
We're going to be doing a lot of calculations with the dates particularly,
trying to get metrics for different months.
And to make this a little easier,
we're going to make sure that we've got a little function that returns just the month.
So, we're just going to quickly type in heading month,
and then using the MONTH function,
I'm going to click on call date,
and that will return the month of the call.
And then, I'm going to do the same thing for the year,
except this time, I'm going to use the YEAR function.
Those are both complete, so I'm just going to use my fill handle to copy down.
Because I'm going to be working with a lot of these columns on a regular basis
to make my calculations a lot easier and quicker to create,
I'm going to use named ranges.
So I'm going to press control A, and then,
control shift F3, and I do want to use the top row, so, okay.
What I've done now is name each of these columns.
And you'll see this makes the calculations much simpler.
And finally, to ensure that the data range will automatically adjust
and my calculations still pick up any additional values,
I'm going to put this in a table.
So this is just control T and then, okay.
Now, I don't want such bold formatting so I'm going to simplify that.
And then very importantly,
I'm going to come up to the table name and I'm going to give it
a more user-friendly name and I'm going to call it tbl_calls.
So now, my raw data is prepared.
I want to start doing some calculations.
And I'm going to do these in a new sheet called calcs.
Many dashboards and certainly,
in the case of call centers,
we'll need some benchmarks or target data to compare the results to.
So the first thing Alex has done is entered
the target values established by the business.
For example, they're looking to get a resolution rate of 65 percent.
If the business themselves are going to enter the targets,
we would probably put this on a separate sheet.
But as Alex is putting them in,
we're not going to worry to do that.
Now, coming down to our summary data,
these are some of the main things we need to calculate before we can even get started.
And the first one we're looking at is the start date.
Because this dashboard will be re-used in six months time with completely different data,
we're going to need to calculate this.
And this is very simple, we're just going to use our MIN function and it's our call data.
We've named the column so nice and easy.
We also want to get our total calls.
And this is just going to be the number of rows in our newly created table calls.
Now, average monthly calls is a little trickier,
because we're going to need to get the average of the number of calls per month.
Now, when you're confronted with the more complex calculation like this,
it might be easier to break it down into a few simple calculations,
and that's exactly what we're going to do.
When we get into our monthly section,
we're going to need a breakdown of the metrics by month anyway.
So, we're going to start those calculations here and then we're going to
use our monthly core calculations to get our average monthly calls.
So, first thing we're going to need is our start date.
So I'm going to click in A33, type equals,
and click on our start date,
and then click enter.
We're then going to use our EDATE function so I'm going to type equals
EDATE and we're going to add one month to our start date.
And then, we can copy that down for the following few months till we've got six months
worth of data and there are six months good to go.
It may be handy to have a month name for when we come to do our charts.
So, for this, we're going to use our TEXT function.
Click on our date and we want that in a month format.
So I'm just going to put four M's in there and then tab.
We're also going to need our actual month number,
which we will use to compare with the data in the table.
So I'm just going to click on that month,
and tab, and then, we're also going to need a year.
Just make sure you choose the function not the named range.
And finally, we need to calculate our monthly calls for that month.
Now, because we're only getting six months worth of data,
we won't get any duplicates in the month number
so we don't actually have to worry to check the year as well.
So we're just going to do an equals and we're doing a COUNTIFS,
and the criteria range we're checking is our month column.
And this time, I do want the named range,
and I'm checking to see if it's equal to the month.
So, for July, you can see those were our total calls.
Now, I'm just going to copy those all down,
and we're started to generate the metrics
that we're going to need to show our monthly breakdown.
Now, we can also complete
our average monthly calls so we're just going to come back up here,
type in equals average,
and select our monthly calls.
So we've started putting
some calculations in place so we can generate the values we need.
The next thing is to look at how we can actually get those values
to populate our graphical elements on our dashboard.
So coming back to our dashboard,
we're just going to expose some of these cells temporarily.
So I'm going to select those columns and choose no fill.
Now even though this is going to be hidden data,
for maintenance purposes, it is really important that you still label your cells.
So this first one is going to be calls per
month and the next one's going to be our average on speed.
Now, I don't actually want to put the calculations in here,
I just want to pull the values through so they're easily accessible to the dashboard.
So, I'm going to type in equals,
come back to my calcs,
and click average monthly calls.
You'll notice that I have the calculation for my ASA directly under that,
so I can just drag that down.
And then, I don't actually want any decimal places so I'm just going to remove those.
So I now have the values in the sheet,
but how do I get them into the actual graphics themselves?
Obviously, I could just have a cell and put it in,
but again, that limits our scope in terms of moving things around.
So, instead, I'm going to click on my shape,
come up to my formula bar,
type equals, and click on the cell that contains the value I want.
I can't put our calculation in here but I can put a cell reference.
And when I click enter,
it is now pull that value directly into my shape.
And all I need to do now is add a little bit of formatting.
So, I'm going to make the size 24,
and it's quite important with your sizes,
think about them, and once you've selected, be consistent.
And that is how you can pull your data into your dashboard using graphical elements.
So now our data is prepared.
The next step is to start constructing
our dashboard in earnest and that's what we'll look at in the next video.
Dr Yvonne BreyerDirector, Learning and Teaching
