WEEKDAY function: How to use & example

Introduction to WEEKDAY function

In the diverse world of Google Data Studio functions, the WEEKDAY function is a simple yet powerful tool. This function is designed to return a number, representing the specific day of the week for a given date. If you are analyzing a vast array of data that involves dates and time, this function can enable you to systematically categorize your data based on the weekdays.

Syntax of the WEEKDAY function

WEEKDAY function can be expressed in two main forms.

1)

markdown
WEEKDAY(date_expression)This syntax involves a Date value as the input.

2)

markdown
WEEKDAY(X [,Input Format])In this form, 'X' can represent a Text field, Number field, or compatibility mode Date. 'Input Format' specifies the format for 'X' and it is optional, provided 'X' is correctly formatted as a semantic date field.

How does the WEEKDAY function work?

At its core, WEEKDAY function interprets a date input and returns a value representing the day of the week. The counting begins from Sunday, designated as '0', and runs to Saturday, designated as '6'. This way, a unique value is assigned to each day of the week, facilitating the analysis of data based on weekdays.

However, it is important to note that how WEEKDAY identifies the day depends essentially on the input's format. For example, for a Text field/expression or compatibility mode Date, many valid strptime formats are accepted, along with certain specific format like 'BASIC', 'DEFAULT_DASH', 'DEFAULT_SLASH', etc.

For a Number field/expression, the accepted formats are 'SECONDS', 'MILLIS' (milliseconds), 'MICROS' (microseconds), 'NANOS' (nanoseconds), 'JULIAN_DATE' (days since Epoch).

Demonstration of the WEEKDAY function

Now, let's take a look at how the WEEKDAY function works using a sales data set.

Assume you have a data set "Sales Metrics" with a field "Date of Sale" where the sales dates are recorded. Now, you want to find out which weekday corresponds to each recorded date.

Example 1 - Processing a Date field

markdown
WEEKDAY(`Date of Sale`)The function will return a number (0-6) representing the day of the week for each date in "Date of Sale."

Example 2 - Processing a formatted Text value

If you have a formatted Text field "Sale Date" like '2022/01/01-09:40:45':

markdown
WEEKDAY(`Sale Date`, 'DEFAULT_SLASH')This function will return 6, representing a Saturday.

Example 3 - Processing a Number value (in seconds since Epoch)

If you have a Number field "Sale Timestamp" representing seconds since Epoch like 1672272000 (representing January 1, 2023):

markdown
WEEKDAY(`Sale Timestamp`, 'SECONDS')This will return 0, representing a Sunday.

Limitations of the WEEKDAY function

The WEEKDAY function is quite flexible with the formats of the input data but it does have certain limitations. Most importantly, it cannot process mixed types of data. Your input data for a single run must all be of the same type (Date or compatibility mode Date, Number, or Text).

Handy Tips for Using the WEEKDAY function

Here are a few tips to effectively use the WEEKDAY function:

• Make sure the input data is appropriately formatted. Check the official documentation for the accepted formats.
• Use the values returned by the function for categorizing your data based on weekdays, for analysis such as which weekday saw the highest sales or the lowest.
• You could also use the results for visual representations in your reports, like bar graphs or line charts, demonstrating the variance in sales for different weekdays.

Remember, understanding your analytics is all about interpreting your data correctly, and Google Data Studio functions, such as WEEKDAY, can be key tools in your analysis toolkit.