PERCENTILE
:
Introduction to Looker Studio Function: PERCENTILE - Understanding its Functionality, Limitations and Effective Usage
NULLIF
:
Understanding and Utilizing the NULLIF Function in Looker Studio for Comprehensive Data Analysis
Mastering the DATETIME_SUB Function in Looker Studio: A Guide to Manipulate Date and Time Information in Your Datasets
In the plethora of functions available in Google Data Studio, DATETIME_SUB stands as a powerful yet straightforward function to wield in order to manipulate date and time information in your datasets. The primary function of DATETIME_SUB is to subtract a particular time interval from a specified date or even a date and time expression. This allows you to effectively cycle back in time from a given date or datetime field. This function, with its remarkable utility, becomes significant while performing operations such as historical comparisons.
Usage and Syntax
Utilizing DATETIME_SUB is as simple as understanding its clear syntax. It follows the pattern:
plaintext
DATETIME_SUB(datetime_expression, INTERVAL integer part)Please note that the 'part' units include MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR — all providing you with a base for subtracting your preferred timeframe.
Examples
Illustrating the use of DATETIME_SUB becomes simpler with real-life scenarios. Given below are sales-related examples using DATETIME_SUB:
- Suppose we want to compare this month's sales with that of exactly one month ago:
plaintext
DATETIME_SUB(current_date, INTERVAL 1 MONTH)- To review last quarter’s performance on products:
plaintext
DATETIME_SUB(Product_Release_Date, INTERVAL 1 QUARTER)- To calculate the time frame since the last promotion:
plaintext
DATETIME_SUB(Promotion_End_Date, INTERVAL 1 DAY)Limitations
Though invaluable, DATETIME_SUB function has limitations. It does not support compatibility mode date types. Additionally, special handling is required when using MONTH, QUARTER, and YEAR parts at (or near) the last day of the month. The result will automatically be the last day of the new month if the resulting month has fewer days than the original date.
Tips
In using DATETIME_SUB function, always remember to specify your 'part' value in capitals as the function is case sensitive. And when dealing with date expressions, it's ideal to use the date_parse function to convert the string to a date or datetimes before utilizing DATETIME_SUB.
