Calculate monthly average between 2 sets of dates using Looker

Looker Q&A Looker
, ,
1

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

Thanks in advance

2

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()

3

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.