How to exclude the value or prevent the calcature when use difference() if previous value is null ? Sencond

Hi dear,
I have a requirement about difference(). When I use this method with the record(1,2,null,null,5,6), the result will be(1,null,null,3,1). I don’t expect the fifth value 5 will substract the second value 2. Can I hava it to be 0 or null instead. The result was expected as (1,null,null,null,1) or (1,null,null,0,1).
from(bucket: “ems”)
|> range(start: 1673712000)
|> filter(fn: (r) => r._measurement == “T3_5s”)
|> filter(fn: (r) => r[“_field”] == “ac_history_negative_power_in_kwh” and r.system == “1” and r.type == “2”)
|> aggregateWindow(every: 15m, fn: last)
|> pivot(rowKey: [“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> difference(columns: [“ac_history_negative_power_in_kwh”])

@Jay_Clifford could you help me solve it , please?

I am not sure if this is possible based on the logic of difference()

@Anaisdg do you have any ideas?

Hello @Jacobsz
You could use timeShift() to shift the rows one position over and then map() to calculate the difference and store the value in a new column

Unfortunately null is working as expected