CODEWITHSUNDEEP
looker-studio
davecut85
davecut85

Reputation: 263

Data Studio - Grouping by Week

I have a simple Data Studio table consisting of two columns. The first column is the week (ISO Year Week) and the second column is the total registrations we've received for that week.

However, my Week column repeats 7 times (7 Rows) for each week as it's counting by day within that week. See below:

enter image description here

Is there any way to get this to group by the listed week? Below are my settings:

Dimension = Conversion Date set as "ISO Year Week" for the type. Metric = Equals the count of Conversion Date (Same Conversion Date field used for dimension)

Any help would be much appreciated.

Upvotes: 4

Views: 12358

Answers (2)

Sami Belaid
Sami Belaid

Reputation: 69

I made a similar case work using blended data.

Your column "Conversion Date" repeats the same week 7 times because it's just a display value. Every row has a date value (year, month and a day) but you're just showing the corresponding week. So, data-studio treats them as different data and doesn't group them.

To be able to group them by Week you need to create a new field with a value containing only the week and the year. So, you can use the formula

YEARWEEK(your_date)

The resulting Date will be groupable.

NB1: If your date isn't of the type Date, you can parse it from text to date using the method:

PARSE_DATE("%Y-%m-%d", your_date_text)

NB2: If the created field has the type number and doesn't show the possibility to change type to Date, you can do this trick: (it's weird but it works):

  1. First type as a formula for the created field and apply:

MONTH(your_date)

  1. This will unlock the compatibility Date types. You can choose from them the ISO Year Week type.
  2. and then change the formula from MONTH(your_date) to YEARWEEK(your_date) [your formula] as I explained above. The chosen date type won't go away even if it wasn't available the first time.

Upvotes: 2

alexkaessner
alexkaessner

Reputation: 2908

There might be an issue with the date format of the source. Without knowing the source (e.g. Google Analytics or Sheets) it’s hard to tell.

Blended Data

I recently had this issue with blended data. The response of a similar question helped me to find a way.

Basically you have to add a new custom field to the data source with the formula WEEK(date_field_link). Data studio will recognise this as a date in compatibility mode, but for me it still works. Then you can use this new date field as a join key to blend the data while grouping it in weeks.

Normal Data

If this problem appears in a regular non-blended dataset you might want to check if Data Studio correctly catches the date as a date. This help article from Google might be worth checking out: https://support.google.com/datastudio/answer/6401549?hl=en#zippy=%2Cin-this-article

Upvotes: 2

Related Questions