Original subtitles

In this lesson.

We're going to continue from the previous lesson.

And in the previous lesson we created a new field called Weekday.

Now we had this number from one through to seven, and we've summed up the sales for that.

Well, one of the questions we were asking was how do we know if this is a monday or Tuesday or Wednesday?

Now, might be far better to actually have the name of the actual day in there rather than just a number.

And really the same applies for your month as well.

We currently got a month number, but you might want to have the month name there.

So in this lesson we're going to be looking at how we can create those names.

So we're going to go back to our data and move across a bit.

So we've created a few new fields now a year, month, day, and we've got a weekday.

What we're going to do is we're now going to create a new column and we're going to say that this is

going to be all week, day nine.

We'll just give that a capital letter we then name.

Now we are going to use a different function here.

So we're going to use a function called equals format.

Now, if you're familiar with your Excel functions, you might know a function called equals text.

Equals text would actually be the way that we would do this in Excel.

But as I say, index, we're going to be using equals format.

So I'm going to start start typing that EFO and you can see that we get the format function.

So let's select that and again we get our parentheses.

However, you'll see that it does have a couple of options.

So it's asking what is the value, what is the format?

And then the column name is in your square brackets.

And what that means is it's optional.

So really this is saying when when you're setting your format, you can have the column names that can

be used.

I'm not going to be using them.

It is an optional extra.

So the first part is what are we formatting?

So in this case we still formatting our sales date.

So again, I'm going to use my left square bracket.

I'm going to go down to my sales date and we're going to select that and you'll see that we do get some

additional options here.

We don't need that.

We just need the cells that field.

So I'm just going to press comma, and that means now we move on to what format.

And you can see at the top it's turned blue, which is telling me I've now moved across to that argument.

Now, just like in Excel, we're going to use our quotations, so I'm going to select my quotations

here.

So just put our quotations in and we're going to tell it how to format the weekday name.

Now, if I use four D's and it doesn't really matter if I use Capital DS or small DS, but if I put

four D's in here, you're going to see that I get the full name of the actual weekday.

So let's show an example of that.

Close my quotations, close my parentheses.

So just to review, we've got equals format.

We're saying we're formatting the sales date and we've got four D's in there that's telling me that

I'm going to get the full name of that.

So let's press enter and you can see we've got the weekday name now, it's Wednesday, Monday, Wednesday.

So this is far more useful when we're trying to understand what is the actual weekday name.

If I was to use three DS, for example, let's just remove one of those D's.

So we just got three D's center.

You'll see that I got the shortened version of this.

I just get the first three characters.

So in this case, I'm actually going to keep it as the first three characters.

So we're going to go in and we're going to actually go back to our report.

Now we're going to have the weekday name here, and while we've got the weekday field at the moment,

let's drop the weekday name in there just to add it in.

Let's just grab it in there.

So now you'll see that weekday one is actually Sunday, two is a monday, Tuesday, Wednesday, Thursday,

Friday, Saturday.

So there we've got now the actual report showing me my weekday name.

However, if we were to remove our weekday field, let's just remove that out.

You'll see that things suddenly changed.

So you see that certainly in this weekday name now changes to Friday and Monday, Saturday, Sunday.

It's actually lost its chronological order.

And the reason for this is that the system does not know that this is actually a weekday name field.

It's actually just looking at this text.

Now, when we hit the weekday in here, let's just pop that back in again.

That was being used to actually sort it.

So when we sought our weekday.

Let's just say from 1 to 7, then you'll see that this is in the correct order.

Okay, So this is a bit of a challenge.

If we're not using the weekday, how do we actually now get the weekday name to be in the correct chronological

order and not in an alphabetical order?

So again, we do get an option from Microsoft to help us with this.

So when I click on my weekday name field and go to the column tools, you'll see something called sort

by column.

Now, this does give you other options that you can use sort by four.

But one of the main reasons for this is to be able to sort your week names, your month names into the

correct order.

And what that means is I can actually say that we are sorting our week then name by week day.

So it's telling which field to actually sorted by.

So just to review that again, we've now got a weekday name selected.

We've gone to sort by column and we're going to tell the system, we're going to sort this field by

the week, day field.

Select that.

And you'll now see that the weekday name is now chronologically correct, because Sunday, Monday,

Tuesday, Wednesday, Thursday and Friday.

So that's how we actually do this.

So that's all week then name.

What I'm going to do is we're going to create a month name field as well.

So I'm going to run through the set of processes that we've just done, just to show you another example

of this.

So let's go back into our data and let's create a new field and we're going to get a new column again.

Please remember new column.

And in this case, we're going to be calling this by month name.

So we're going to say again, equals in this case, we're going to be using all format.

So choose format.

And again, my value is my sales date.

That is what we're using format, this comma.

Then it asked me what is my format?

Now, in this case I'm working with a month name.

So what I actually use is my quotations and if I use for EMS again, it doesn't matter if you actually

use more.

I could have for EMS this more use my quotations, close my parentheses and again, you probably guess

this already, but when I press enter I get the full month name because I'm using for EMS.

However, if I go back into this, delete one of the EMS press that I get the shortened version.

So just remember with weekday name, we use these for the day, for month, we use EMS.

Okay, So that's looking good.

We've now got our month name.

Let's go back into our report.

Let's create a new table.

And we're now going to use our own name in this.

So let's drop my name in there.

And we're going to now drop ourselves in there.

And you can see straight away we do have a problem because the it is alphabetical in terms of the way

that it is sorting this.

So just like we did previously, we are going to use that sort by field.

So we're going to choose our month name on it.

Just remember to click on the field, name your column, Tools will open and we're using the sort by

column.

And in this case we're saying the month name is going to be sorted by the month number.

So the month number, remember, is this field called month.

So we're just going to select that.

And now you'll see that that is chronologically correct.

So again, just to review that, click on the month name.

Go to your soap bar column.

And then we're picking the month number as the soap bar.

So I hope this makes sense and that you can see how we can use the soap bar to be able to get the correct

order for our weekday names and for our month names.

It's a little bit of a workaround, but once you've set this up, once in your daily model, you don't

need to do it again and it will then always default.

It doesn't matter if you're creating a graph or if you're creating a table.

Actually, let's just show a quick example of that.

So if I now created a graph, let's show a column graph.

And because we've now got the month name in the right order, when I drop my month name into my axis,

let's say ourselves in our Y.

There you can see now that we've correctly got these in the correct order.

So there we go.

That's an example of how we use those fields.

And the next lesson, we're going to be moving across to using a date master.

One of the reasons for that that we're going to see is that as you start working with this, you can

see that I'm starting to build up quite a number of fields.

And this can take quite a bit of time when you're setting up your data model.

So sometimes you might want to just set this up once and then import it into your system.

But let's cover that in the next lesson.

I will see you there.