Lag function on string data type

I have sensor data with me where the sensor values received should be counted if it’s previous sensor indicator is off and it’s sensor value is not null. For example,
if(isNotNull(sensorValue) AND lag(sensorIndicator) is ON) then return me 1 else 0 but I am not getting a way how to apply lag function over a string field. Pls suggest me the ways to do so.

Hi @Nikhitha,

The lag function calculates the lag (previous) value for a measure based on specified partitions and sorts.

The function works only on Measures and returns values for previous in measure. Consider changing the value of ON and OFF to one through calculated field.

Example something like: ifelse(sensorIndicator=‘ON’, 1,0)
and then use this newly created calculated field as part of the lag function.

Hope this answers your question.


1 Like

hi @Srikanth_Baheti , thanks, it worked but the problem now is as I am using the calculated field in the lag function and that lag function in another calculate field of ifelse function, it is forcing me to include the columns used in the lag function in the group by field wells which is not required for me. For example,

1st calculated field: sensorIndicatorInt = ifelse(sensorIndicator=‘ON’,1,0)

2nd calculated field: count = ifelse(max(ifelse(isNotNull(sensorValue),1,0))=1 AND lag(sensorIndicatorInt,[receivedtime ASC],1,[id,sensortype])=1,1,0)

I need count column only to be displayed in my visual but I had to use sensorIndicatorInt, receivedtime, id, sensortype in group by field well otherwise throwing me error where sensorIndicatorInt shouldn’t be used for the grouping.

Hi @Nikhitha,

Yes, you will need to add all the columns used in the calculation. But you should be able to hide the unwanted columns from the visual.
Example below: