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
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.