[SOLVED] Synthetic Variable - Difference between two difference device's variables

Data management
1

I have what I hope is a simple use case:

Sensor 1 counts cars going into a park
Sensor 2 counts cars leaving the park

Every hour the two sensors connect to Ubidots and post their totals. I am hoping to use the difference between these two numbers to estimate how many cars are in the park.

I can compute the difference with a synthetic variable using fill_missing() but, it is calculated twice every hour:

  • once when the first sensor reports and filling in the hour-old data from the second sensor
  • again when the second sensor reports with the counts from the same hourly period - this is what I want

I tried to use the When() functionality (original post edited with Sergio’s help):
where (fill_missing({{var1}}.timestamp - {{var2}}.timestamp) < 300,000, fill_missing({{var1}} - {{var2}}))
I am assuming that the timestamp is in milliseconds so this should be 5 minutes.

If the difference is more than 5 minutes or if one sensor does not connect in an hourly period, I don’t want to create a synthetic variable “dot” for that hour.

But I still see two “dots” created each hour as if the where() conditional is not being applied. Is there a better way to do this?

Thanks, Chip

2

@All,

Sergio found the issue. I should have added a test to preclude a negative value for the time difference which was causing the new data points to be generated. Here is the solution in a multi-line format which is easier to read:

a = {{variable1}} ;
b = {{variable2}} ;
c = fill_missing(a.timestamp - b.timestamp);
d = fill_missing(a - b);
where((c>0 and c<300000), d);

This solution will only create a new datapoint if the two readings are within 5 minutes of each other.

Thanks again to Sergio for his help!

2 Likes