Video summary
The video introduces a specialized data visualization technique designed to compare raw population figures with derived coverage indicators, such as vaccination rates. The primary goal of this exercise is to ensure that the calculated indicator never exceeds 100%, which would indicate a logical error in the data. Ideally, the numerator representing the number of doses given should not surpass the denominator, which represents the estimated number of surviving infants or the total population. If the raw data shows erratic behavior where these values cross over each other, it suggests potential issues with data quality, such as incorrect calculations for either the numerator or the denominator, or the use of an inappropriate population figure.
To demonstrate this concept, the presenter sets up a chart using immunization coverage data alongside the specific raw metrics that form its basis: doses administered and live births. Initially, these three data points are plotted on a single axis, which makes it difficult to interpret because the scale distorts the relationship between the percentages and the absolute numbers. The solution involves configuring the chart with multiple Y-axes, placing the coverage rate on a secondary axis while keeping the raw counts of doses and live births on the primary axis. This dual-axis setup allows viewers to clearly see if the line representing doses given ever crosses above the line representing live births, which would visually confirm an impossible scenario where more people were vaccinated than existed in the population.
By analyzing the trends over a five-year period, this visualization serves as a critical quality control tool for health data systems. It enables analysts to quickly identify inconsistencies where coverage rates appear to spike above 100% or where the numerator unexpectedly eclipses the denominator. When such anomalies occur, it signals that there is likely a problem with how the data was collected or processed, requiring immediate investigation and correction. Ultimately, creating charts that juxtapose component parts of an indicator with the final calculated rate helps maintain data integrity by ensuring that reported coverage rates remain realistic and consistent with the underlying population dynamics over time.
Read the full video transcript
Okay. So what I want to do in this uh
next kind of exercise is just create
this visualization. Um this can be a
useful visualization where you're
comparing basically um raw data with
your population figures along with the
indicator that you would derive um from
this raw data. And what you're looking
for is that um your indicator in this
case it's a coverage rate does not
eclipse 100%. And likewise, you're
looking for your numerator not to
basically
um eclipse your denominator for some
reason or other types of erratic
behavior to emerge, right? Um the
behavior I showed in that textbook
example was, you know, the population
spiking up, the coverage increasing over
100%. Um and then things going back down
and then up again. You know, it just
wasn't really consistent um with what
you would expect. Now, there's no data
quality issue here in this example. um
which is good. That's would be the case
in your own system or in other examples.
Okay. So,
just open this up. Okay. So, what we
have here is the BCG coverage and then
we have a couple different denominators,
right? We have the number of doses given
and the denominator for this would be
the um estimated number of surviving
infants. Okay. Um, and then I've also
added the live bursts on here as an
extra comparison, but we'll exclude it
from our example. Um, we'll just add in
the BCG coverage, the doses given, and
survival under one. And you can see
there's multiple axis um axes on this uh
multiple y-axis um on this chart. One is
the data value um for the raw data and
one is the data value for the indicator,
the coverage rates. Okay. Um this is
also a very useful chart uh type to
create. Okay. So for my data once again
we'll select BCG coverage doses given
and I'll exclude uh the live burst
that's not so important here but I will
include the survival under one we'll
look at this for the last 5 years okay
in the total overall country. Okay. So
just go back to another tab in
visualizer and I will make a new chart.
Okay. So for the data, I want um a
couple different pieces of data. So I
want immunization coverage. So I can
select that from here. And I am looking
at just to remind myself my BCG
coverage. Okay. So I'll add that in. And
then I want some of the raw data
associated with this. I want my BCG
doses given. And I want the denominator
for this indicator, right? um which in
this case is um uh we can use live burst
or or uh just depending on how our
indicator is set up.
So I'll go to my data elements this time
and just double check I'll open this up.
Okay. So, we'll get BCG do is given and
we'll get [snorts]
just one of these.
And let me take my
we'll take the lepers in this case. Why
not?
Okay. So, I'll update my chart or sorry,
I selected a pivot table. So, let me
switch to a chart. Okay. So, I'll start
with a column chart and then we'll kind
of go from there. We want it to look
like this actually. So, maybe I'll start
with a line chart and then we'll alter
it to fit our needs.
Okay. So, right now I have a line chart,
but it's not making a whole ton of
sense, right? Also, we want to switch
our periods. So, I'm going to do that. I
don't want the last 12 months. I'm going
to select let's say years.
We can say maybe this year and the last
five years. Okay. So, our period looks
good now. But the problem is um we have
uh you know one representation um axis
on the y-axis for our data and the
percentages look kind of uh off as a
result of this. So, we're going to go to
options and then axis.
I'm sorry, series is what we're looking
for. Okay. And here we can add in
multiple axes um for our data. Okay. So,
we have coverage which is one type of uh
data which is our percentage and then we
have these doses given and our live
bursts. This is raw data. These are our
numbers, right? that would make up our
numerator and denominator for this
example. So what I want to do is I want
to put the coverage on its own axis. So
I'll just say axis 2 and I'm just going
to keep it as a line in this example cuz
I want to make sure basically that I'm
not eclipsing 100%. Right? Um in for my
coverage rates. So once I do that and I
update now we see we have two axis. We
have one on the left hand side one y-
axis on the left hand side. This
represents our raw data on the right
hand side. This is for our coverage
rate. Okay. And then we can see here
this is our doses given. And we don't
want this dose given line to go over our
population line. Okay. In this case,
that's this blue line here. Okay. If we
did see that, then we would start seeing
coverages above 100%. Which is something
we don't want to do. So all we're doing
is looking at our numerator,
denominator, and the subsequent
indicator together in order to make sure
that one that that the pattern um over a
certain period of time is correct. Now,
of course, if we did detect coverages
more than 100%. If this bottom line do
given went over my denominator line,
okay, then there's either a problem with
the numerator or the the denominator. Um
and we would have to sort that out,
right? Um because you know it's not
possible to vaccinate more people than
exist essentially, right? Um and that's
the issue when you eclipse 100%.
So I think it's a good idea to just try
making this type of visualization. And
this is just one example. Um you can
think of other indicators, other
coverages and you can break them down
into their component parts, right? And
you can plot the numerator, the
denominator and the indicator value
together using this dual axis type of
chart. And you can check, you know, um
does the behavior between periods look
consistent? Um does the for some reason
does the numerator eclipse the
denominator and therefore you get a
value of of 100%. Um for some reason
maybe the right denominator is not being
used in that example or maybe um either
the numerator or denominator is not
calculated correctly. um and needs to be
fixed. Um so those are all kind of
things you can look at using this
visualization.