Custom Time Range for Summing144

PigFarmer private msg quote post Address this user
Wow, Mike we met at CTO a couple weeks ago. Fantastic tool - mad props for your efforts here. I think I have determined how to do a lot of the basic stuff at this point. I am getting bogged down on some more advanced stuff (or at least advanced for me).

How do you sum data points from a custom period of time? Example: Say I have a propane tank on my hog barn. It is filled with 1,000 gallons on 11/12/14 at 13:00. I have a propane sensor that computes gallons consumed and reports into GS. I want to sum that data from 11/12/14 13:00 on to know how many gallons I have left in my propane tank.

Can you set it up for the expression to just automatically find that fill timestamp in the tank stream and then sum the data from then on or does that have to be done in a custom application through an API? Does anyone have any thoughts on how to do this?


Post 1 IP   flag post
MikeMills private msg quote post Address this user
Hi Brad, thanks for the feedback. Great use case. Sometimes the easy ones are the toughest to model. We'll see with this one.

How does the sensor compute gallons consumed? Is it reporting gallons/hour every hour (or some other period) or does it report an amount at random times?
Post 2 IP   flag post
PigFarmer private msg quote post Address this user
Its going to be uploaded to GS at random times. This will be a similar case as feed bins.
Post 3 IP   flag post
MikeMills private msg quote post Address this user
The randomness of both streams makes it a bit of a challenge, but we think we have a solution. But it requires a small enhancement. We'll try it out and post the solution when we know it works - probably a week or two.
Post 4 IP   flag post
MikeMills private msg quote post Address this user
Here's how I modeled it (with a new enhancement we just added).

I've added you to the live organization so you can play around. If anyone else wants access to this org, then send me your email address and I'll add you to it.

Tank Capacity: Point stream that holds the tank capacity (1,000 gal)
Filled: Regular stream that manages random fill times from the sensor
Consumed: Regular stream that manages random consumption amount times from the sensor
Consumed Running Total: 5 minute interval stream that derives a running total of the amount consumed since the last fill.
Tank Level: 5 minute interval stream that derives the tank's level

The most complicated part is dealing with values that arrive at random times. To simplify things, I use 5 minute interval streams and use those for the calculations. It doesn't have to be 5 minutes, I could have used any interval size down to 1 second, but I estimate that "knowing" a tank level within a 5 minute period is good enough.

The Running total stream is where complex calculations are done. We created two new derivation options to achieve this:
* Perform derivation when any variable is NULL
* Perform derivation when Any dependent values have arrived.

Without these two options, derivation will not run until all values have arrived that are used in the expression.

I also enabled automatic gap filling for the Running Total stream. This will fill all previous intervals that were not derived with the previous consumption amount. This is needed so that "cons_previous" has a value that we can use to sum with the "cons" interval when it arrives (at a random time).

The expression will check if there is a "filled" value that has arrived for the current 5 min interval being derived. If not, then it is assumed a "cons" value has arrived and it is handled. I reset the running total to zero when "filled" arrives.

Here's the expression:

/*Consumed Running Total. This will run each time after one or more of the three samples arrives */
/*Relies on this derived stream to have gap filling enabled to carry consumption forward*/
if (isNull(filled), 
   if (isNull(cons_previous) && isNull(cons), 
      NULL,                                         /*should never reach here*/
      if (!isNull(cons_previous) && !isNull(cons),  
         cons_previous + cons,                      /*increment consumption*/
         if (isNull(cons_previous), 
            cons,                                   /*first fill*/
	    NULL                                    /*should never reach here*/
   0                                                /*tank filled - reset consumtion to zero*/

I've put in some dummy data. All of the consumption and fills take place within one day and just happen to land on the start of an hour.

Post 5 IP   flag post
2847 5 5
Log in or sign up to compose a reply.