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

Daily average from monthly database

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello everyone,

 

I have imported this Google Spreadsheet database into my project and I needed to create a daily average per month graph out of a metric. My original database goes something like this:

 

 

Month (AAAAMMDD) | Metric
2010/01/01 | 2346
2010/02/01 | 2776
2010/03/01 | 3232
2010/04/01 | 3507

 

It goes on basically up to today (and keeps being updated).

 

I would need the daily average for every month displayed on a graphic. I could simply divide the metric by 30, but it would inaccurate given that there are months with 28-31 days. 

 

Perhaps adding another row with simply the name of the date and a Case statement to divide accordingly --and Feb. depending on the year due to leaps. It seems a lot for a simple issue.

 

So, how's the best way can I do that using Data Studio?

Daily average from monthly database

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Hi Raphael, I'm looking to do the same thing within a data studio project. Did you ever find a solution to this?

Daily average from monthly database

Follower ✭ ✭ ☆
# 3
Follower ✭ ✭ ☆

Hello,

 

You have to copy your date and set it to a YYYYMM format.

Then copy your metrics and set it to number with average treatment.

 

It will do the work!

Daily average from monthly database

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hmm tried that with (MM) format and it didn't work, will give your solution a shot if I get the chance. I ended up just doing the work in a separate sheet in Google Sheets. Thanks for your reply Smiley Happy

Daily average from monthly database

Follower ✭ ✭ ☆
# 5
Follower ✭ ✭ ☆

Yes you can't do it with a MM format, because if you set a YYYYMMDD into a MM format, Data studio will take the first YY as the MM... So it won't work.

Re: Daily average from monthly database

[ Edited ]
Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

@Greg R

 

As a matter of fact, I kind of did.

 

First I created a metric containing the number of days each month had, like this:

 

CASE WHEN REGEXP_MATCH(Mês_no, '(1|3|5|7|8|10|12)') THEN 31 WHEN REGEXP_MATCH(Mês_no, '(4|6|9|11)') THEN 30 WHEN Ano  NOT IN ( '2012' , '2016' , '2020' , '2024' , '2028' , '2032' ) THEN 28 ELSE 29 END

It's in Portuguese, "Mês_no" is the number of the month. "Ano" means year.

 

Then I created a second metric which divided the number I wanted by this first metric.

 

I wanted to get the daily average of confirmed subscriptions per month. So it went something like this:

 

Confirmed subscriptions / number of days a month

It isn't pretty, but it actualy works. And it's good up to 2035 hahaha

 

 

@analytics a, I'm not sure I understand what you mean. Could you, please, elaborate more?

Daily average from monthly database

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Thanks for your reply Raphael, that looks like a promising way to do this. Will need to keep this in mind for future. Thank you!