CODEWITHSUNDEEP
looker-studiodate-rangeweekend
Vector7
Vector7

Reputation: 107

Google Data Studio Ignores Saturday and Sunday when Indicating Yesterday in Custom Date Filter Default Range

I am creating a report in Google Data Studio which pulls numerous days-worth of data; however, I want the report's date filter to default the initial presentation data to ONLY display the previous BUSINESS DAY's data.

I can set the default date range to "Yesterday" (or today minus 1) using the Advanced option. That get's me part of the way; however, the overnight, batch data I received each is based on activity conducted the prior weekday (Monday-Friday).

So, a date range of "Yesterday" (or today minus 1) works find when my report is executed on a Tuesday through Saturday, but if it is executed on a Sunday or a Monday, no data will show.

Is there a way Data Studio can handle this scenario? Essentially, I need "Yesterday" to ignore weekend days of Saturday and Sunday.

Upvotes: 1

Views: 3582

Answers (2)

Moses Stevens
Moses Stevens

Reputation: 1

You can do something like this:

CASE
    WHEN (date_field = DATETIME_SUB(current_date(), INTERVAL 4 day) AND weekday(current_date()) = 1) THEN DATE(DATETIME_SUB(current_date(), INTERVAL 2 day))
    WHEN (date_field = DATETIME_SUB(current_date(), INTERVAL 3 day) AND weekday(current_date()) = 1) THEN DATE(DATETIME_SUB(current_date(), INTERVAL 1 day))
    WHEN (date_field = DATETIME_SUB(current_date(), INTERVAL 1 day) AND weekday(current_date()) = 2) THEN date_field
    WHEN (date_field = DATETIME_SUB(current_date(), INTERVAL 3 day) AND weekday(current_date()) = 2) THEN DATE(DATETIME_SUB(current_date(), INTERVAL 2 day))
    ELSE null
END

and then use that as your date range dimension. It basically makes Fridays and Thursdays look like yesterday and the day before on Monday and Friday the day before for Tuesday so you can use custom data ranges with the yesterday and previous period filters

Upvotes: 0

Caesar Oweitu
Caesar Oweitu

Reputation: 11

So I got this answer from Google Support

  1. Click the pencil icon to edit your data source
  2. Duplicate your date field and change the type to Day of Week
  3. Add a control (type Drop-down list) to your dashboard.
  4. Set Date range dimension to Date
  5. Set Dimension to Day of Week and default selection 1,2,3,4,5.

Upvotes: 1

Related Questions