Welcome to this final lesson in this module.
Previously, you discovered how to convert between discrete and
continues dates and why you would want to switch from one date type to the other.
After this lesson, you will be able to create dates using
calculated fields for those moments when a date field is not available.
So, we will continue to use the superstore data set.
Now, we will first open that calculated box dialog window lets right
click anywhere in the data window or sidebar and click on Create calculated field.
If you right clicked on a particular dimension or measure to create your calculation,
then it will appear in the calculated field window in the formula text area.
To filter the functions to show only the date functions,
let's use a dropdown box and select date.
Now that we got our function selected,
let's first define the potential values for DATEPART,
which is used in many of the date functions.
DATEPART can hold the following values.
Seconds, 0-60.
Minutes, 0-59.
Hours 0-23.
Days, 1-31.
Weekdays, 1-7 or by name such as Sunday.
Week, 1-52. Day of year, 1-365.
Month,1-12 or by name such as January.
Quarter, 1-4 or a four digit representation for year.
Now that you know the different values,
let's examine the date functions.
The DATEADD function allows you to specify a portion of a date and increase it.
The number that you use for the interval will change the date by increasing the DATEPART.
The DATEDIFF function allows you to return the difference between date
one and date two expressed in units determined by DATEPART.
The start of week parameter is optional and if it is not
defined then the start of week is determined by the associated data source.
The DATENAME function returns a DATEPART parameter of the date as a string.
Again, the start of week parameter is optional.
The DATEPARSE function essentially works in reverse of DATENAME
by converting the string into a date time with specified format.
If the string does not match the date time format,
then it will return a value of null.
The DATEPART function allows you to return a specified DATEPART as an integer.
Again, the start of week parameter is optional.
The DATETRUNC function truncates date to
the accuracy of the DATEPART that you specify in the function.
In other words, it rounds towards that DATEPART.
The DAYDATE function returns a day of the specified date as an integer.
The ISDATE string is a logical test that is
also included in the list of logical functions.
It tests a strange determine if it is a valid date,
a true, or false.
The MAX function exists in several categories of functions including the date functions.
The MAX function returns a maximum of a single expression
across all records or the maximum of two expressions for each record.
The two arguments must be the same type.
This function will return a value of null,
if either argument is null.
Like the max function above,
the MIN function is commonly used as a number function but can also be used with dates.
The MIN function returned the minimum of a single expression across
all records or the minimum of two expressions for each record.
MIN returns the value of No if either of the two arguments is null.
The MONTHDATE function returns
the month of the specified date as an integer just like the DAY function does for DAY.
The NOW function returns a current date and time,
the TODAY function returns a current and date.
The YEARDATE function returns a year of the specified date as an integer.
You can use any of these functions to create a new date variable to use in your analyses.
Let's look at an example.
If you want to create a variable that shows today's date,
we can use the NOW function.
And let's change the name of our variable to today's date.
Now we have a variable that will show us today's date in our dimensions.
So far in this course,
you have learned the difference between discrete and continuous dates,
how to use date hierarchies,
how to convert between discrete and continuous dates,
why you would want to switch from one date type to the other,
and how to create dates using
calculated fields for those moments when the date field is not available.
Remember to continue to practice everything you are learning. See you next time.
Suk S. Brar, M.B.A.Lead Business Consultant
