Original subtitles

So our sales manager is back again and he's got some further questions.

And these are going to require some filtering of data to give him the answers.

So I've deleted the pages that we were just using for the filters.

But for this one, what I'm going to do is actually just create a new page and we're just going to call

this off full to activity.

Let's just try that again into renaming and we're going to say this is going to be our full to activity.

Guess at the first question we've been asked is how many cities have a profit?

Over 150,000, and what is the total profit?

Right.

So basically we're going to be using tables.

So let's select a table quite nice and big.

We want our cities in there and we also need our profit.

Right.

So the question is how many of these cities actually have a profit?

Over 150,000.

So I'm I'm going to be doing counting.

So I'm actually just going to add another version of my city.

And we're going to set that to a distinct count so I can actually count.

So we can see there's a lot of cities.

We've got 263 cities in total.

Okay.

Let's find out how many have a profit.

Over 150,000.

So in this case, I can use my sum of profit.

So I'm going to go into my filters.

And going to go is not less than in this case we want it's greater than.

So we're going to say it's greater than 150,000.

And we're going to apply the filter.

And we can see now that there's 19 cities that have a profit, over 150,000, and their total profit

is 16,277,215.

Let's look at the next one.

What is the total profit for cities that begin with the letter SE?

So I'm just going to remove that sum of profit filter.

We're going to go back to our list of cities.

And in this case, I'm going to use a more advanced filtering because we want to find everything that

starts with the letter SE.

So we're going to say start with, say, SE and apply the filter on that.

In this case, it tells me there are 23 cities that start with the letter s and its total profit is

$3,211,670.

Okay, We've got a new table to create.

We're going to create a table displaying the sales for each product name.

What is the total sales for audio product category and how many products are in there?

So a couple of things to change there.

Let's just delete that.

Let's start with a new table.

And again, it's just a big a lot of products.

And we also need to know what is the sales values.

Drop that in there as well.

In this case, we want to know for the audio product category.

And how many.

So I know I'm going to be counting.

So let's just add the product name again to our distinct count.

And there we go.

We should see this 1638 products, which is correct.

So we're going to be using the data fields because we don't actually have the product category at the

moment.

So we're going to drop that in there at data fields.

And in this case, we just want to know for the audio product category.

So we can see that there's actually 106 products and the total sales is 1,029,381.

Right.

The next question is also about product names.

So it wants to know how many products contain the word TOSO and have sales greater than 100,000.

So we can stick with product name and sales, so we can actually stick with this table.

We're just going to remove this filter.

Actually, we can delete the filter because product category is not needed.

And in this case we said the product name needs to contain.

We're going to be using an advanced filter contains the word contain.

So so we're going to say column two.

So we spelled that correctly.

Our filter.

There we go.

We've got 536 products.

However, anyone those that have sales greater than 100,000, let's just collapse that.

Let's go to our sales.

And in this case we want to say is greater than I'm going to say 100,000.

If I fall to.

And there we go.

We've actually got 14 products that meet that criteria.

The next one is a date one.

So it wants to know what was the total sales for the 1st of January 2013 to the 30th of June 2013?

So again, going to remove those filters.

If you take that out.

And I could do here is actually I don't need my product name and my I just need my total sales.

So let's keep that and there's no filters.

So we're just going to bring our audit data in.

And in this case, we want a advanced because we want it between two dates.

So in this case, we want to say is on or after 1st of January.

So we're going to set one slash.

One, 2013.

And also is on for the 30th of June.

So 36.

2013.

So hopefully I got my dates correct, formatting the filter.

And you can see that the total is 8,359,314.

Okay.

Our last question, How many countries sold over 50 different types of products?

Okay.

So let's change this again.

So let's remove that audit date.

And we need to know in this case, it's country.

And it's product name.

So we need to know how many products have been sold in each country.

So remember, we use that distinct count earlier for this.

So we're now counting the number of products per country, but we only want to know how many countries

had over 50.

So again, I'm going to need to count my countries.

Let's just drop that in there.

Let's go count distinct.

We've got 34 countries, but we need to know the count of the product name is actually over 50.

So we go count product name and in this case is saying greater then we want to say is over 50.

So we'll select that apply filter.

So it looks like there's 17 countries that had over 50 product names.

So there we go.

There's the answers that for our sales manager, we're going to conclude the activity and I'll see you

in the next lesson.