Combining different measurements with different resolutions with multiple math operators

Hi,

I have three measurements, each with one, two or three fields:

production:

  • production

grid:

  • to_grid
  • from grid

price

  • sell_price
  • buy_price

The resolution for grid and production is 1hour, the resolution of the price are 1month.

Now, I want to calculate the the following: (production - to_grid + from_grid) * buy_price on a daily basis.

The production, to_grid and from_grid should be summed up but buy_price should be forward filled to each day, based on the month.

I can’t wrap my head around how to do this in flux.

Any ideas?

Kind regards,
Jesper

What do you mean the buy_price should be forward filled to each day based on the month. Can you please explain?

I would first consider writing all of this data in one measurement so that you can:

  1. use the pivot() function to turn all your field values into column
  2. use the map() function to perform the math you want

Since your fields are in different measurements you’ll have to do some more work. Specifically, you’ll need to join your data from different measurements first. You’ll have to perform two joins and then you can pivot and then you can map.

Out of curiosity, can you tell me more about your work? I’d love to learn about what you’re working on. I find it cool to learn about.

So the idea is basically that the buy_price comes every month, but it is valid for the rest of the month. So when I try to calculate the my function on a daily basis, I figured I need to resample the buy_price up to daily values, before doing the calculation - but they all have the same value, hence the forward filling.

The issue with one measurement, is that they come from different data source, so it is not so convenient to put in same measurement. I suppose I could make task to combine them or something like that.

My biggest issue is the combination of resampling the data as well as joining it, I would love to see an example of that.

It is not very difficult, I am basically collecting electricity consumption/production from a house with a solar installation and trying to combine the data with electricity prices to evaluate the cost-benefits of the solar.

1 Like

@gedemagt

Wait that’s a super cool project.

if the data was in different measurements I would do the following:

import "array"
option task = {name: "daily calculation", every: 1d, offset: 10m}

production = from(bucket: "mybucket")
  |> range(start: -task.every)
  |> filter(fn: (r) => r["_measurement"] == "production")
  |> sum() 
  |> findRecord(
    fn: (key) => true,
    idx: 0,
)

grid = from(bucket: "mybucket")
  |> range(start: -task.every)
  |> filter(fn: (r) => r["_measurement"] == "grid")
  |> sum() 

to_grid  = grid 
  |> filter(fn: (r) => r["_field"] == "to_grid")
  |> findRecord(
    fn: (key) => true,
    idx: 0,
)

to_grid  = grid 
  |> filter(fn: (r) => r["_field"] == "from_grid")
  |> findRecord(
    fn: (key) => true,
    idx: 0,
)

//I'm not sure 
buy_price = from(bucket: "mybucket")
  |> range(start: -1mo)
  |> filter(fn: (r) => r["_measurement"] == "production")
  |> last() 
  |> findRecord(
    fn: (key) => true,
    idx: 0,
)

calculation = (production._value - to_grid._value + from_grid._value)*buy_price

to_write = array.from( rows: [{_time: now(), _measurement: "my calculation", _field: "calculation", _value: calculation},

|> to(bucket: "destination bucket") 

I made some assumptions about what you’re trying to do, primarily around the fact that you want to find one value daily. Please let me know if that’s not the truth an then well work with joins, unions, or maps as needed.

Otherwise here are the docs to the functions I used