Caculate daily average

[mig17]

Hello everyone.

So I found this is a recurrent question here on Looker Community, but I simply cannot make it work.

I want to calculate the daily average value for a certain metric.

I have to calculated the difference between two date variables and obtain the variable “Time difference in hours”,

Then my data looks something like this:

Date             | Time difference in hours

27-03-2021 | 5

27-03-2021 | 7

27-03-2021 | 8

26-03-2021 | 10

26-03-2021 | 12

Now I want to have the data like this:

Date             | Mean (Time difference in hours)

27-03-2021 | 6,66

26-03-2021 | 11

How do I do this?

Thank you !

[Dawid]

You can create measure of type average that would automatically calculate this for you. How is the data modelled in LookML? Any particular reason why this has to be a Table Calculation?

[mig17]

I should have mentioned that I am new to Looker 🙂

Thanks for the help Dawid. Indeed the Table Calculation did not allow me to go where I wanted. I made use of the field “Custom Dimension”

Just for the record, this is how I did it.

1st: Add the variable I want the data to be grouped by. In this case I selected my variable date.

2nd: Go to Custom Fields. Add a “Custom Dimension”. Use there the function diff_hours(time_variable1, time_variable2)

3rd: On the custom dimension, access the 3 dots and create a measure that is the average of that.

4th: Done!

Br

[Dawid · Accepted solution]

If it’s a field that will get used rather often, you can consider moving it to the LookML and use dimension_group of type: duration

It would automatically  create the durations with easy to read way, and then you could add a measure.

https://docs.looker.com/reference/field-params/dimension_group

Here is a good example, which I think would fit your purpose:

[mig17]

Thank you very much Dawid!