Data Studio
5K members online now
5K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

So I've been playing around with Data Studio today. Excited! Here's a bunch of questions on stuff I'd like to do but can't figure out how to:

1. By default the system seems to pick a date dimension. Data is aggregated by day. How can we see weekly or monthly data?

2. I have a BigQuery table that has about 15 million rows. Some rows have bad data (i.e., metric=0). The aggregation level for that metric in the data source connection is defined as "Average". How do I exclude rows where metricValue = 0? I tried to create a custom field but the formula does not have an IF option.

3. Any way to do a custom aggregration for a metric? I'd love to see median and percentile. 

4. I don't see "Count" as an aggregation option in Edit Connection for any of the Number fields. Does it only show up for certain kinds of fields? 

5. In the Welcome to Data Studio tutorial report, drawing a table from the [Sample] World Population Data shows the population numbers as 10X their true values. e.g. China is 13 billion and U.S.3,076,528,567. Is the underlying data wrong or is this a Data Studio error?

6. Can we create Metric filters? e.g. show me a scatter plot of Revenue and Conversion rate [metrics] by Traffic source [dimension], but only where pageviews > 1000.

7. How do you link a filter control to a chart? I created a scatter plot of Revenue (X), Conversion Rate (Y) and Content Group [dimension]. Then I created a filter control with dimension Content Group and metric Pageviews. Toggling various options in the filter does not affect the scatter chart.

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Nikhilesh J
July 2016

Re: How to specify time aggregation unit (day/week/month), and other questions

Google Employee
# 2
Google Employee

Hi Nikhilesh!

So glad to hear you're excited about Data Studio! (I am, too! Smiley Happy

Those are all really good questions, and I'm not sure I have an answer for each one but I'll try:
1. Data Studio doesn't have aggregations for dates (yet, I think). I'll check on that, but for now, my suggestion would be to use the DATE functions to create a calculated field that breaks out the date parts you need. Check out the function reference in the Help Center.

 

2. There is no IF function, instead, there is a CASE function, perhaps that would help exclude the bad data. Another thought is to use a filter control in the report. Do be aware that although Data Studio is free, you'll still incur normal query costs for BigQuery requests.

 

3. I don't think Data Studio supports custom aggregations. Sounds like a cool feature request!

 

4. Looks like Count and Count Unique are only for non-numeric fields. A work around might be to create copy of that field, then use the CAST function to make it a string. (I haven't tried this myself, so you might need to get creative).

 

5. Wow, you're right! I'll pass that on to the developers. (But glad to see you were able to follow along with the examples Smiley Happy

 

6. Other folks have requested metric filters, so I'm hoping we see them at some point. But at the moment, I don't think they are supported.

 

7. Sounds like you've already mastered filter controls! From what you've written, I'd expect it to work. By any chance is the chart grouped with some other component(s)?  If so, try grouping the filter control with the chart, or ungrouping the chart. Grouping controls with charts limits their scope to the group.

 

Hope this helps!

 

Rick

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Nikhilesh J
July 2016

Re: How to specify time aggregation unit (day/week/month), and other questions

Google Employee
# 2
Google Employee

Hi Nikhilesh!

So glad to hear you're excited about Data Studio! (I am, too! Smiley Happy

Those are all really good questions, and I'm not sure I have an answer for each one but I'll try:
1. Data Studio doesn't have aggregations for dates (yet, I think). I'll check on that, but for now, my suggestion would be to use the DATE functions to create a calculated field that breaks out the date parts you need. Check out the function reference in the Help Center.

 

2. There is no IF function, instead, there is a CASE function, perhaps that would help exclude the bad data. Another thought is to use a filter control in the report. Do be aware that although Data Studio is free, you'll still incur normal query costs for BigQuery requests.

 

3. I don't think Data Studio supports custom aggregations. Sounds like a cool feature request!

 

4. Looks like Count and Count Unique are only for non-numeric fields. A work around might be to create copy of that field, then use the CAST function to make it a string. (I haven't tried this myself, so you might need to get creative).

 

5. Wow, you're right! I'll pass that on to the developers. (But glad to see you were able to follow along with the examples Smiley Happy

 

6. Other folks have requested metric filters, so I'm hoping we see them at some point. But at the moment, I don't think they are supported.

 

7. Sounds like you've already mastered filter controls! From what you've written, I'd expect it to work. By any chance is the chart grouped with some other component(s)?  If so, try grouping the filter control with the chart, or ungrouping the chart. Grouping controls with charts limits their scope to the group.

 

Hope this helps!

 

Rick

Re: How to specify time aggregation unit (day/week/month), and other questions

Google Employee
# 3
Google Employee
Hi Nikhilesh,

I found out what's going on with the Population report: those numbers are the SUM of all the years of population data (the data set is year by year population figures from 2011 - 2014). So to see the latest population for the data set, add a filter to the table for the year 2014.

To extend the example even further, you could add a filter control to the report using the Year as the dimension. Then, you could see each year you select.

Hope that helps!

Rick

Re: How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭
Hey Rick,

Thanks for answering those questions!

1. Too bad that date aggregations aren't there yet. Google Analytics has them and that has made me expect it from every analytics tool in the world Smiley Happy The DATE aggregation functions were a cool idea but unfortunately there is no function for WEEKNUM. There are functions for year, month, quarter, weekday, and even hour, minutes and seconds.

2. Thanks for the pointer to the CASE function. Excel has IF and BigQuery/Postgres have CASE. I was just using the wrong paradigm Smiley Happy
The problem with CASE is that the documentation says only dimensions are allowed in boolean expressions. So it won't work for excluding rows where a metric has invalid values.

4. (Count aggregation not appearing): Cool idea to create a text duplicate of the id by using CAST. Unfortunately, it's still not giving me any dropdowns for aggregation for the new text field. "None" is displayed by default.

7. Grouping the filter control with a chart makes it work. Tried it and works like a charm. Perhaps the documentation should make it clear that

Re: How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭
For #2, could you create a new "Is Valid" dimension with CASE logic to create true/false you could then use to filter the data?

Re: How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭
Thanks, Jeff. I wasn't aware of the CASE function but found out about it from Rick E's answer above.

Re: How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭
Hi Rick

I hope you're well. Has there been any development with the Week time dimension? Or maybe a link to a complete function that I could copy and paste in my reports?

Thanks

Michael

How to specify time aggregation unit (day/week/month), and other questions

Google Employee
# 8
Google Employee

Hi Michael

 

Sorry for the very late response (stumbled back across this thread looking for something else). 

 

You may already be aware but we now support 2 week formats: ISO Year Week (e.g.,

Aug 23, 2016) and ISO Week (e.g., Week 35).

 

You can learn more here: https://support.google.com/360suite/datastudio/answer/6401549

 

Best!

 

Rick

How to specify time aggregation unit (day/week/month), and other questions

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Hi Rick,

 

When I want to show my data week on week (value per week number), the date range automatically sets the x-axis to the whole year (week 1 to week 52).

 

How can I apply the date range based on weeks to show only certain weeks? 

 

I use week number, and I also have a date in it. Selecting the date range based on "date" does not give the desired effect, it does not drop the x-axis on weeks. 

 

--> I can select Time dimension "weeks"

--> I can not select date range dimension "weeks" only date

--> I do get a graph with week numbers on x-axis and values on y-axis

 

 

Hope my question is clear! 

Re: How to specify time aggregation unit (day/week/month), and other questions

Google Employee
# 10
Google Employee

Hi Pieter,

 

If I understand your question, you want the date range control to offer an option like "Last n weeks" or "week x to week y." Is that correct? If so, then unfortunately, that's not available. 

 

It sounds like you have figured out how to create a chart with weeks in the X axis, yes? Data Studio is going to show all the weeks, 1 through 52, even if there's no data. 

 

What you might try is having a "Week" dimension with numbers 1 - 52. Then, you could have a dimension filter based on Week. Use a combo chart instead of the time series.

 

Sorry, these are just suggestions. I don't have a definitive answer.

 

Best!

 

Rick