[MUSIC]
Okay, with our data newly cleaned the next step is to understand the data.
In particular, to understand a little bit better about amount because
that's one of the key things we're going to look at for our control test.
So I think the thing we want to do is first probably organize this a little bit.
And so I'm going to go ahead and highlight all of these and
maybe drag them down just a little bit so that we can see things easier.
All right, put that there.
The next thing I'd like to do is demonstrate a container tool.
This helps make things a little tidier as well as we start to
have a bit of a larger workflow.
So here I create a container I'm going to call this ETL Part A.
And there we go.
I can minimize this now and make it small so
that it's easier to see what's going on in the workflow that we have.
I can also do something that I will not do here but
that if I click here I can disable the container, okay?
And then it won't run at all.
Now I won't do this here because we need all of our steps to run so
that we can have the data.
So that's one option there.
If you have a piece of the workflow that you're not using, you can disable it.
And so it won't run at all, saves a little bit of time.
I'm going to rename this one as well over here
in the configuration panel area as ETL Part B.
All right so the next thing I'm going to do then is bring in
a tool that will help me look at a summary of the amount column.
And that's the field summary tool.
It doesn't show up easily here.
So I'm going to search for it.
So I'll click on the search box and I'll type in field summary and
this is the one I want and look this is nice.
Another nice feature of all tricks.
I can just drag this tool here.
And it's going to work for me.
I'm going to pull it down so that it's a bit lower here so
I can work on things right there.
Okay so I'm going to go ahead and pull that in and I'm going to look at it over
in the configuration panel and again I'm interested in amount only.
So I'm just going to click on a mountain and I don't want to sample and
put down I want to look at all of it and I want to look at summary statistics for
the amount column.
And I'm just going to run and so we'll go through everything and run and
once that's completed and you can see starting to take a little bit longer.
I've got three options I've got the field of summary tool I've
got option o which is output.
I've got options r here which is the reports and I've got i.
So with i and r you have to bring in a browser tool and
we're not we don't need to do that.
Let's just look at we'll click on o and we'll see some statistics.
Some how statistics for the amount column you can see it named here.
You can see it's numerous and it gives you these great statistics.
There's several things that we want to point out here that they're interesting.
First the minimum amount here if we look at the minimum amount it's negative.
So this seems unusual you know as we contemplate what amount represents that is
amounts the value of the purchases on the p card.
Thus any negative amount must represent either a mistake or a refund of some sort.
Certainly in addition to the control test we do in
the future we're going to the internal auditor here.
We definitely want to gain a better understanding of these negative amounts.
Probably run some additional tests on them.
The ardor would would inquire for management why the negative
amounts exist and then test these amounts to verify what management tells them.
Okay so the second thing we would notice here is that the maximum value is
pretty high.
Remember that we have a control in place that the maximum transaction value for
anyone transaction is supposed to be 5000 or less.
So we already know just by looking at the summary statistics here that one of our
controls has been violated.
So clearly we there's a lot of work for the auditor to do here.
So the next thing we're going to notice is the median and the mean.
So here we have a mean of 87.65 and
the mean of 258 and a half about.
So this means the mean being higher than the median indicates that the data is
right skewed.
Meaning that there's several outliers on the high end of the distribution or
in other words there's a few abnormally high values.
So the order might do some additional testing on these high values by asking
management about them.
And gathering supporting evidence for them to examine and
verify that they're valid transactions.
So next let's continue to examine the distribution of the amount column by
looking at box plot or a box and whiskers plot of amount.
This plot is an easy way to graphically examine the media and
inter quartile range of amount.
It allows us to verify some of the things that we saw in the field summary tool.
So let's go to report here and let's see if we find something that helps us out.
And sure enough if we look right here we have interactive chart,
let's go ahead and use this.
We'll pull in and here I go pull that in from our clean data.
And so on the left here now in the configuration area you
see this configured chart.
And so this is nice,
it's going to give us a bunch of different options to do and things to look at.
So let's just start with, we'll add a layer, okay?
And let's go ahead and
we'll call this layer just called a box plot of amount because.
That's what we want to do and then it gives us a type option and
we want a box and whiskers plots will pick that.
So there's that option and
then x we're going to leave blank because we're just a box plot.
So we're just looking at one variable and obviously want amount here.
And so here we have a box and whiskers plot box plot and
we can look at the amounts that we have here.
So so the maximum is 23,000 as we saw before in the other tool that we just
looked at.
We can see the medians 88 which that sounds about right if
I remember the meeting that we have this negative minimum.
So things look great so far, although visually this is not very helpful, is it?
So what we want to do is let's go ahead and scroll down to x.
Okay let's go over here two axes and
change the display range there it is 4y?
So let's customize this and let's just set it to like negative 1000 and
1000 and see what that does for us.
See if that makes things look a little bit better and indeed it does.
Again, if we hover here, we can see the same amounts from before.
But this is this is pretty helpful.
We can see the rights que nous as we look at the bigger area here above the median.
And so that's actually quite helpful.
So let's go ahead and get out of this and there's no good way to view
this unless we keep clicking on configure chart or we can output it.
So let's use our brows tool right here.
And so if we run this then we'll create that chart the way we just
configured it will take a minute of course.
But we can go and click here and we have our chart over here in
the configuration area and we can pull it over and there we go.
There's our chart.
Next let's visualize the amount of transactions that happened each month.
That is let's sum up the transactions for each month and visualize that.
To do this we'll need four separate tools.
So first we'll need the summarized tool because we can we want to summarize
amount in two months, we want one entry,
one, total sum of all the transactions by month.
We can do this with a summarized tool.
Next let's sort months from 1 to 12.
So we can visualize the data by month.
After that, we'll use the interactive chart tool again and
then we'll use the browse tool again so we can visualize things more easily.
Let's go ahead and move this down.
And let's go grab the summarized tool.
Let's see, so that's going to be under transform, here's summarize, okay?
So that's what we need to pull this over.
Let's go ahead and bring the data in our clean data in.
All right, let's go over then and total amounts for each month.
So we want to first group by month.
So let's click on month right here and that's why we created that variable.
Let's add a group by here. So they were grouping by month.
That's what we want.
And then we want to sum up amount within each month.
So let's click on amount and we'll add a some and we'll rename this to some amount.
I just make it lower case.
You could call this whatever you wanted.
Okay, so that's done there.
Our next step then, is to sort and we want to sort by month from 1-12.
So that's in our preparation area.
So we go to the preparation area, we pull down sort and
we're just going to sort by month ascending yeah, so 1-0.
The next thing that we're going to do is we're going to
use our interactive chart tools.
So let's go back and find that under reporting.
And here we grab it from interactive chart, we pull that over.
All right, so let's go then over to our configure chart.
Again, we want just to look at I think a bar chart would work well here.
So let's just call this sum by month.
And let's try a bar chart.
We think that look good.
So the X axis is going to be month, right?
Because we just want to look at one bar, the height of each bar for
each month and then we want to use sum amounts.
And so that will make sense.
And before we can change that we'll have to run this.
So let's go ahead and run that so we can get the data all the way down here so
that we can get it sorted and ready to go for our tool.
It's nice that we click on the tool here, we have no errors at all.
So that's great.
And so things appear to be sorted.
And if we kind of look at the data we can make sure.
And let me pull this up just a bit and we can see that sure enough, we
have each of our 12 months and we have the sum of the transactions for each month.
Again, a reason to use the visualization tool is because this gives us the data but
it's not super easy to see and understand.
So let's go back to our configure chart and so that looks pretty good, right?
And this is much more discernible now, we can see quite a few things and
draw some conclusions from there and maybe give some steps for
the auditor to take in the future.
One thing that might be useful is if we go in here to layer and
let's kind of put a different color for each month.
So if we do that, if we go into variable, we can do this.
We can select a month and, select some interesting color scheme that we want.
You don't know what you're going to pick.
You can pick whatever you want, maybe blue,
whatever your favorite color is and do that.
Okay, so that's done.