Daily Material Usage Calculation from 6 AM Level Readings

Data management
1

Hi,

I’m trying to create a synthetic variable to calculate daily material usage from a radar level sensor in a silo. Here are my specific requirements:

  1. Get one reading per day at 6 AM

    • Need to handle cases where there isn’t an exact 6 AM reading (ideally use the closest reading, the max within an hour time window of 6 am, or fill from most recent reading before 6 am).
    • Current sensor sends readings approximately every 15 minutes

  2. Calculate daily usage by:

    • Comparing consecutive daily 6 AM readings to determine usage

  3. Current challenges:

    • My attempts using max(), last(), and fill_missing(), and offset functions are outputting multiple values nstead of once daily.
    • Having trouble isolating just the 6 AM readings
    • Need help with the correct date range function syntax for this use case

What’s the correct approach to get exactly one reading per day at 6 AM?

Thanks in advance for your help!

2

Hello @qntfy,

I hope this message finds you well.

Could you please tell me which device and variable are you using for this synthetic expression?

I will be attentive to your response.

Alejandro

1 Like
3

Thanks @Alejandro, I messaged you privately with details.

4

Thank you @qntfy, I received your message.

I have a few questions, but first, let me confirm that we are able to take only one reading per day, scheduled at a specific time. In this case, the set time is 6 AM.

However, it’s not clear to me what you want to do with the original variable you sent me privately. I understand that the variable reports data approximately every 15 minutes, but what exactly do you want to calculate at 6 AM? Are you looking to calculate the sum of the previous day’s values, take an average, or perhaps read the first or last value of the day? There are several possibilities here.

My question arises because it’s important to clarify what you want to do with the original raw variable. The good news is that, regardless of the operation you choose, we can use the same structure to retrieve a single reading per day at 6 AM.

This article explains how to configure it using a specific date range and an offset:

Analytics: Synthetic Variables Basics | Ubidots Help Center

For instance, if you want to generate only one value per day, you can use a date range of ‘24H’ or ‘1D’.

image
image834×234 6.08 KB

Next, we can add an offset to the expression to specify the starting hour for generating the first value. For example, by setting the offset to 6, the variable will generate the first value at 6 AM. Here’s a good example:

image
image841×278 10.4 KB

In this example, we’re calculating the sum of a variable every 8 hours, starting at 6 AM. With an ‘8H’ range, it will generate a value at 6 AM, another at 2 PM, and the last one at 10 PM. The cycle will then repeat, with the next value generated at 6 AM the following day.

In our case, if we add an offset = 6, and a range of “24H”, it will generate only one value per day at 6 AM.

Please take a look and let me know if you have any questions.

Best regards,

Alejandro