datetime_sub - Looker and Google Analytics

[lancedobson]

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)

[Mehdi_Oudjida]

@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