I want the ISO Week (Date) in my charts to start on Sunday.

[OmarTox]

Hello, in the date format, I have configured my charts in ISO Week and Year, everything looks fine. But I would like my ISO Week to start on Sunday. Can anyone help me?

[slater · Accepted solution]

Hi @OmarTox , 

You'll need to create a new calculated field in order to do this. You can see the solution here

[AndrewM1982]

This link is broken. I am very interested in the solution as I have Looker Studio data I'd like to display starting on a Wednesday.

[varunikaraja]

Thank you for your query!

Please try the following option:

1) concat(cast(Day as NUMBER) - weekday(Day)," | ",cast(Day as NUMBER)-weekday(Day)+6)

2) In your underlying dwh, create a custom calculation similar to:  DATE_TRUNC(DATE(timestamp), WEEK(SUNDAY)) which is BigQuery syntax

[DariusN]

When you say 'day as NUMBER' and 'Day' in this formula, how do you input that into the actual formula? I keep getting syntax errors.

Also, it's curious. Google Sheets has a weeknum function that starts on Sunday, but LookerStudio week function starts on Monday, why not just add a weeknum function that starts on Sunday to Lookerstudio?

[tommcfarlin]

I had issues implementing what the others suggested, (@slater your "solution here" link is broken). Here's what I did.

1. Switch your chart to "Line chart" (not Time-series). This is represented by an icon with two lines (red & blue).

2. Add a calculated field to your data source with the formula below. In the example, start_date is a date field representing the starting Sunday for the data point. Name it "date_range"

FORMAT_DATETIME("%Y-%m-%d", start_date) || " to " || FORMAT_DATETIME("%Y-%m-%d", DATETIME_ADD(start_date, INTERVAL 6 DAY))

3. In your chart config, set the Dimension to "date_range". Set Sort to date_range and Ascending. This is very important!

[AndrewM1982]

I tried this but it looks like it is setting the range to the last few days dependent on the day you are viewing the data, not a solid day of the week. I'm looking for a solution for a manual data update that only updates on a Wed-Tues range and this looks like it works if I view it on a Wednesday but I'd like to be able to make it every Wednesday.

[tommcfarlin]

@AndrewM1982 ah I can see that. While I haven't tested it, I think a solution would be to truncate the start_date to the desired day of week.

Here's a link to the date functions, see DATE_TRUNC https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_trunc

If I'm understanding your use case, you could replace instances of start_date in my previous reply (step 2) with DATE_TRUNC(start_date, WEEK(WEDNESDAY)) 

I'm interested in hearing if that works for you!

[AndrewM1982]

Thanks for trying but it doesn't look like Looker Studio has DATE_TRUNC enabled, only DATETIME_TRUNC. https://support.google.com/looker-studio/answer/9729685?hl=en

[randa_yandika]

hi, from my side, this calculation only showing daily, can i make it weekly grouping starting, the example is 6 - 12 oct?