Exploring the DAY Function in Looker Studio: Maximising Insights from Your Date Data

 Exploring the DAY Function in Looker Studio: Maximising Insights from Your Date Data

Today, we explore an interesting and vital function within Google Data Studio - the DAY function. This practical yet straightforward tool provides key insights by evaluating and returning the day of the month from any given date.

Understanding The Syntax

There are two different ways to utilize the DAY function, contingent on the data you have:

  1. Extracting the day from a Date or Date & Time value:

    DAY(date_expression)

  2. Extracting the day from a compatibility mode Date value:

    DAY(X [,Input Format])

In both instances:

  • date_expression
    refers to a field or expression that is either in Date or Date & Time format.

  • X
    can be a field or expression which translates to Text, Number, or compatibility mode Date.

  • Input Format
    offers a formatting option for
    X
    . It's optional if
    X
    is correctly structured as a semantic date field.

Gearing Up the DAY Function

Let's now dissect how to use the DAY function.

When extracting the day from a Date or Date & Time value, you simply use the

DAY(date_expression)
syntax and input your date expression.

On the other hand, when dealing with the compatibility mode Date value, you should use the

DAY(X [,Input Format])
syntax. Many input formats are accepted such as
%Y/%m/%d-%H:%M:%S
and
%Y-%m-%d [%H:%M:%S]
, among others, whenever your
X
is a Text field or expression, or compatibility mode Date. For Number fields or expressions, DAY accepts
SECONDS
,
MILLIS
,
MICROS
,
NANOS
, or
JULIAN_DATE
.

Examples in Action

Let's apply this knowledge with some practical sales metrics:

Suppose we have a sales record date and we want to determine the sales day:

  1. For a Date or Date & Time value:

    DAY("2025/12/25")
    This will return
    25
    , the day of the month for the date provided.

  2. For compatibility mode Date value with Text field:

DAY("20211225", "DEFAULT_DECIMAL")
This returns
25
, recognizing December 25th as the day of the month.

  1. For compatibility mode Date value with Number field:

DAY(1609459200, "SECONDS")
This will return
1
as it recognizes January 1, 2021 when the seconds since the Unix Epoch time is converted.

Function Limitations

While hugely beneficial, the DAY function does have some limitations. Due to its design, it only returns the 'day' part of a date; thus, it can't be used to extract other components like month or year from a date. Additionally, it works optimally with compatibility mode dates and upgraded Date and Date & Time data types.

Tips & Tricks

To maximize the DAY function, ensure to correctly identify whether your data is in Date or Date & Time format, a compatibility mode Date value, or a Number field. Utilizing the correct syntax is crucial for the correct output. Additionally, knowing and understanding the various formatting available for

X
in the compatibility mode DATE will glean accurate results.

The DAY function, while elementary in Google Data Studio, deciphers compelling insights from our date data. Breathing proficiency into this function allows for better interpretation and comprehension of your data sets.

More function to use with Looker Studio

FORMAT_DATETIME
:
Understanding the FORMAT_DATETIME Function in Looker Studio for Enhanced Data Analysis and Presentation
ACOS
:
Unveiling the ACOS Function in Looker Studio
UNIX_DATE
:
Using the UNIX_DATE Function in Looker Studio: A Comprehensive Guide to Converting Time-Sensitive Data into Numerical Form
LEFT_TEXT 
:
Understanding and Utilizing the LEFT_TEXT Function in Looker Studio for Effective Data Analysis
REGEXP_REPLACE
:
Mastering the Advanced REGEXP_REPLACE Function in Looker Studio for Effective Data Transformation and Cleanup