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

Days Between Calculated Fields

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

 

I am using Google Data Studio to build client dashboards but having problems for a specific calculated field I want to create. I want to create a new field that automatically calculates the "Number of Days" between two Dates. The Date range will be determined by the user as they pick from the Date Picker in the Dashboard. I want to use the new field in calculations like "Daily Spend" or any other metric requiring an average over a number of Days.

 

Just wanted to add that "Date Range" is dynamic, picked up the user. The Days Between calculated field must capture the start and end dates of the dynamic range and report back daily averages over the selected range.

 

Thank you for any help you provide.

 

Paul 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Paul K
December 2016

Days Between Calculated Fields

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Maybe COUNT_DISTINCT(Date) will give you the number of days. I haven't tried it but I think that would work. 

View solution in original post

Marked as Best Answer.
Solution
Accepted by topic author Paul K
December 2016

Days Between Calculated Fields

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Maybe COUNT_DISTINCT(Date) will give you the number of days. I haven't tried it but I think that would work. 

Days Between Calculated Fields

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Daniel! Much appreciated and I'll test out your solution, which makes perfect sense.

 

I was able to arrive at a solution later in the day of my post. I made the problem much more complicated than it is because I was trying to follow Google's suggested use of the "DATE_DIFF(X, Y)" function where "X" and "Y" represent start and end dates. Google kept stating that the proper format for using this function was:

 

DATE_DIFF(TODATE(end_time, 'JULIAN', '%Y-%m-%d'), TODATE(start_time, 'JULIAN+', '%Y%m%d'))

 

But, the solution was simply meant defining a new calculated field as: Date_Diff(Date-Date) where "Date" is column header for the campaign results for each day of the campaign's period. I needed "Date" because my Dashboard has a dynamic Date range that users can pick to get results between any two Dates of the campaign.


Thanks Again! Paul

Days Between Calculated Fields

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi Paul-

 

I'm trying to do the same thing, but I'm not understanding your solution. 

 

Can you give me step by step?

 

Thanks!

Days Between Calculated Fields

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi,

 

I have exactly the same question as Paul, but i dint' get the solution right... I haven't a lot of experience with Data Studio so maybe I need a step by step guideline. Can anyobody help me?

 

I took the answer from Paul (DATE_DIFF(TODATE(end_time, 'JULIAN', '%Y-%m-%d'), TODATE(start_time, 'JULIAN+', '%Y%m%d'))) Copy & Paste it but I got a error message. Need I to define "start_time" and "end-time" somewhere? Thanks for your help!

Re: Days Between Calculated Fields

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

I could solve it. I used some auxiliary fields. First I defined the Maximum (MAX(Index: Day) und the Minimum (MIN(Index: Tag) number of days in the report (depending on the Datapicker). Then I substract the Minimum from the Maximum and add one. This should work.


Roman B wrote:

Hi,

 

I have exactly the same question as Paul, but i dint' get the solution right... I haven't a lot of experience with Data Studio so maybe I need a step by step guideline. Can anyobody help me?

 

I took the answer from Paul (DATE_DIFF(TODATE(end_time, 'JULIAN', '%Y-%m-%d'), TODATE(start_time, 'JULIAN+', '%Y%m%d'))) Copy & Paste it but I got a error message. Need I to define "start_time" and "end-time" somewhere? Thanks for your help!




  

Days Between Calculated Fields

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

HI +Paul K,

 

I had the same issue and was unable to create the date calculation (please let me know if you are able to find out). The only workaround to calculate current day, or days to the end of the month, was to use a Google sheet as a new data source and import a function that shows Days to end of the month base n the current day.

 

Hope that helps somehow Smiley Happy

 

-Oscar

Days Between Calculated Fields

[ Edited ]
Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

 Maybe COUNT_DISTINCT(Date) will give you the number of days. I haven't tried it but I think that would work. 

 

I experimented with this approach and found that it only works when there is a record for each Date corresponding to the Date Range. For example, if the Date Range is "Last 7", and there are records for 5 Dates, then COUNT_DISTINCT(Date) will be 5.