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

Looker Q&A Looker Studio
,
1

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?

1 Like
2

Hi @OmarTox ,

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

3

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

1 Like
4

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?

5

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))

  1. In your chart config, set the Dimension to ā€œdate_rangeā€. Set Sort to date_range and Ascending. This is very important!
1 Like
6

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.

1 Like
7

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.

8

@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!

1 Like
9

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

10

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

11

Iā€™m not sure if this is overkill, and there is probably a more sleek way to achieve this, but I was able to get this to work using the following formula. This gave me weeks defined as Sunday - Saturday, and ā€œDateā€ is the field that I am trying to translate into a Sun-Sat week timeframe.

FORMAT_DATETIME(ā€œ%Y-%m-%dā€, DATETIME_ADD(Date, INTERVAL -1*WEEKDAY(Date) DAY)) || " to " || FORMAT_DATETIME(ā€œ%Y-%m-%dā€, DATETIME_ADD(Date, INTERVAL 6-WEEKDAY(Date) DAY))

For each date, you figure out what day of the week it is and then subtract the appropriate amount of data to figure out the Sunday the week starts on, and add the appropriate amount of days to figure out the Saturday the week ends on.