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 !
Solved! Go to 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:
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?
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
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:
Thank you very much Dawid!