Question

# Calculate monthly average between 2 sets of dates using Looker

• 2 replies
• 64 views

• New Member

Hi there Looker community

1st post of many to come I think.

Problem statement:
Average amount of time take between 2 sets of date ranges on a monthly basis displayed in a pretty looker visualisation

How do I work out the average of the Diff_Days per month of Date Range 2 and display that on a line graph with month of Date Range 2 on the X-axis and average on the Y-Axis?

Data:

 Date Range 1 Date Range 2 diff_days(DateRange1,DateRange2) 1 2013-09-13 2022-06-11 3193 2 2013-10-11 2022-06-12 3166 3 2014-02-18 2022-06-13 3037 4 2014-05-25 2022-06-11 2939 5 2014-06-05 2022-06-12 2929 6 2014-06-04 2022-06-11 2929 7 2014-06-07 2022-06-12 2927 8 2014-06-11 2022-06-11 2922 9 2014-06-16 2022-06-12 2918 10 2014-06-22 2022-06-11 2911

### 2 replies

Userlevel 7
+1

You can create a measure of type: average and feed it the dimension that has diff_days as a result. Or if everything is in Table Calculations then you need another one with mean()

Thanks @Dawid but…

Your 1st option is utilising the LookML, correct? Ideally i’m trying to utilise the Looker front end for this one.

Your 2nd option is what I was trying and even tried the Custom Dimension and can get the diff_days as shown in the table initially posted, but when I change the data to only display the month it doesn’t display one instance of a month. and then couldn’t get the average of the diff-days per month.
Not sure if i explained that in the best way.