Looker Studio Calculated Fields. Extracting week from date

When you create a weekly report in Looker Studio you can use the ISO Week field type. But it is super long and often you can’t see the full week’s name. Keep up with the article to see how to fix it, or check out the video tutorial:

How it works in practice

Basically, we have a table that contains Date (ISO Year Week), Total users, Active Users, 7-day active users and 28-day active users data. My objective is to extract the first day of each week. 

First way

To create the field for the week will add a new dimension field using a formula:

DATETIME_TRUNC(Date, WEEK)

By default the format of this field is Date & Time, so I just changed it to Date. We can apply this formula to the bar chart as well and it works pretty well. 

There is a little tip: if you want your week to start from Sunday, use Week in the formula, but if you are willing to present the week starting on Monday, you should better use ISOWEEK in the formula. So the formula looks like this:

DATETIME_TRUNC(Date, ISOWEEK)

Second way

There is another way to do it:

= EXTRACT(Date FROM DATETIME_TRUNC(Date, WEEK))

This formula allows us to extract the part from the datetime expression. Below there are notes that may help you decide what expression to choose. I recommend to use this formula if you’re working in Google Sheets.

This is how the table and the chart look in the end!

If you want to know more about Looker Studio, get practical skills and theoretical knowledge about data visualization, I invite you to my Data Visualization in Looker Studio course! You can read more about and join the course by the link.

Hope you liked my article! Do not hesitate to leave your impressions in the comments section!

You can read more articles of mine in my Medium.