1. Time intelligence functions
The last part of this course will cover time intelligence functions.
2. Time intelligence functions
Time intelligence functions enable you to manipulate and compare data using time periods, including days, months, quarters, and years.
For example, a retail store may have more sales near the holiday season at the end of the year, as shown on this line chart. Because of this, retailers may want to check that sales are on track with the previous year's holiday season.
Additionally, companies likely have monthly or quarterly goals. Time intelligence functions allow you to estimate these goals and to benchmark against previous performance.
There are many time intelligence functions, but we'll cover some frequently used ones.
3. Time intelligence functions returning a date
The most basic time intelligence functions are used to return date values. For example, NEXTDAY() returns a column that correlates with the next day.
4. Time intelligence functions returning a date
SAMEPERIODLASTYEAR() returns the same day from the previous year, useful to calculate year over year change for example.
Other functions can be given multiple dates to return a column of dates, such as DATESBETWEEN().
5. Time intelligence functions returning a date
This function returns dates between the given start and end date, which can be helpful when you want to compare values over a limited period of time.
6. Time intelligence functions returning a date
Time intelligence functions that return a date are typically used within another measure as a filter. For example, you could calculate the sum of sales, filtered for a certain time period, which is October 4th till October 26th 2014 in this case.
7. Time intelligence functions returning a date
Another group of time intelligence functions returns a value based on an expression, date field, and an optional filter. A commonly used function is the total year, quarter, or month to date function.
8. Time intelligence functions returning a date
As an example, you can calculate the running sum of all orders since the beginning of the year until today, or until a certain month. Note that "today" is depending on the current day. As a result, you only need to write this function once after which your function will immediately reflect the updated data from the database.
9. Best practices for time intelligence functions
As a best practice when working with time intelligence functions, an established dimensional date table containing a column of all dates should be used.
The reason why we shouldn't use the date column within a fact table is that it may have missing dates and this can cause issues with data aggregation.
10. Let's practice!
Up to you now. I'll see you in the last video!