Using Lag function in lookml measure based on condition

Jharna
Participant I

I want to calculate the time difference for a device that goes out of bound for some time in LookML. For example: If we have a device that should work in the range of say 65 to 80 and if it goes beyond the 80 mark or less than the 65 mark, I want to know for how long the device had that behavior. For the date, I have timestamps like 'YYYY-MM-DD HH:MM:SSSSSS'. So, I have to calculate the time difference in the same column (timestamp) depending on the value of a different column (temperature). Is it possible to do this by creating a measure or dimension in looker? If yes, how?

I want to use the lag function in measure as I have created a custom dimension ‘OutOfBound’ which is a yes/no type. It gives an O/p yes when the device goes out of range.

0 1 1,816
1 REPLY 1

kuopaz
Participant IV

As far as I know, windowing functions such as LAG can only be used in a derived table. Which is a restriction as I find them very helpful in SQL.

Top Labels in this Space
Top Solution Authors