I have a reporting window that is from Wednesday (inclusive) to the following Wednesday (exclusive).
I want to have a calculated metric for the start of reporting window as a date based on a datetime field called Created.
In gsheets, it would look like this, where 4 is the offset to get to Wednesday and 7 is the days in a week:
FLOOR( (Created - 4)/7 )*7 + 4
In Looker Studio, I would expect the following to work:
DATE_FROM_UNIX_DATE( FLOOR( (UNIX_DATE(Created) - 4)/7 )*7 + 4 )
but I get the following error:
Invalid formula -
Function "DATE_FROM_UNIX_DATE" doesn't support DATE_FROM_UNIX_DATE(NUMBER).
Function "DATE_FROM_UNIX_DATE" supports DATE_FROM_UNIX_DATE(INTEGER).
Adding another Floor() around the argument does not seem to convert it the number to an integer.
The Cast() function does appear to support casting to integer either.
Maybe DATE_FROM_UNIX_DATE needs to be less strict and just perform the cast internally?
Or even better, date_from_unix_date could accept a float or double and calculate the time as well.
This ended up working, by avoiding division entirely:
DATE_FROM_UNIX_DATE( Unix_date(Created) - weekday(Created) + 3 )
LinkedIn
Twitter
