datetime_sub - Looker and Google Analytics

Looker Q&A Looker Studio
1

I am trying to return a Weekly Date Dimension since the ones out of the box for GA4 in Looker studio aren’t great.

I used to be able to do this much more easily. I am using this formula:
datetime_sub(Date,interval weekday(Date) DAY)

It seems I can’t use Date as the first argument in the function, but I’m not sure how to formulate this. I’ve tried a number of these, but can’t seem to figure it out. Not even sure how to diagnose it.

(I have spent 2 hours with chatgpt to work on this)

2

@lancedobson

It is not documented but to use a dynamic interval, you have to cast the value as an Integer and not just use the number outcome of weekday function.

datetime_sub(Date,interval CAST(WEEKDAY(Date) AS INT64) DAY)

I hope it helps.

Feel free to tell me if you need more info, otherwise you can accept this answer as a solution.

Mehdi

1 Like