Square and Square root

InfluxDB 2
,
1

Hi,

I am using Grafana + InfluxDB 2.1.1

I am trying to derive the Power Factor of my system through:

  1. Extract “kVArh” & “kWh” data from InfluxDB database.
  2. transform through “spread()”
  3. Derive the Power Factor through the following formula:

Power Factor = square_root( kVArh^2 + kWh^2)

my current code is as below but have no clue how to move forward:
import “math”

from(bucket: “EnergyMonitoring”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “DPM”)
|> filter(fn: (r) => r[“Device”] == “DPM_TNB_MainIncoming”)
|> filter(fn: (r) => r[“Location”] == “MSB_ROOM_1”)
|> filter(fn: (r) => r[“_field”] == “Total kVArh” or r[“_field”] == “Total kWh”)

2

Looks like you already have a (working?) query for #1. #3 should be able to be done via a map() function (we’ll get to that later). What about #2? Are you trying to find two different spreads (kVArh and kWh)?

1 Like
3

Hi @grant1 ,

Thanks for your note.

Yes. I can get the spread by adding “spread()” after the query. However I have no idea how to proceed further.

The goal of getting the spread is to have the difference between the value of both kVArh and kWh at the beginning and the end of the period selected. Let’s call it delta_kVArh & delta_kWh. We then plugin the numbers to the following formula to calculate the Power Factor:

Power Factor = square_root( delta_kVArh^2 + delta_kWh^2)

and then display only the Power Factor as a result.

I managed to find the “Spread” but
I have no clue how to move forward after that.

4

Another point I did noticed is that there is a “space” in my field name “Total kVArh” and “Total kWh”

Which I have no idea how to declare it if I were to use

|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({
r with
_value: math.sqrt((r.Total kVArh * r.Total kVArh) + (r.Total kWh * r.Total kWh)),

}))

5

Are the above two field values stored in InfluxDB with the exact same timestamp?

6

Yes they are stored with the exact time stamp.

7

@ZE_RU_ENG

Before I propose a query, I just wanted to point out that Grafana does offer a transformation called “Difference” which seems to be what you are after (this is not the same as the Flux difference() function).

image
image894×698 38.6 KB

However, the fun stops there, because Grafana cannot do the remaining math (exponents or square root), so let’s move back to Influx…

Based on what you described, are you sure that spread() is the correct function to use? From the link:

spread() returns the difference between the minimum and maximum values in a specified column.

Assuming you want the delta between the first and the last values (not the maximum and minimum values), and bearing in mind that I am still very new at this myself, maybe this?

import “math”

First = from(bucket: "EnergyMonitoring")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r[“_measurement”] == “DPM”)
  |> filter(fn: (r) => r[“Device”] == “DPM_TNB_MainIncoming”)
  |> filter(fn: (r) => r[“Location”] == “MSB_ROOM_1”)
  |> filter(fn: (r) => r[“_field”] == “Total kVArh”)
  |> first()
  |> yield(name: "First")

Last = from(bucket: "EnergyMonitoring")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r[“_measurement”] == “DPM”)
  |> filter(fn: (r) => r[“Device”] == “DPM_TNB_MainIncoming”)
  |> filter(fn: (r) => r[“Location”] == “MSB_ROOM_1”)
  |> filter(fn: (r) => r[“_field”] == “Total kVArh”)
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
|> difference()
|> map(fn: (r) => ({r with delta_kVArh_squared: r._value * r_value}))

// Now do the same as above, but with kWh //

First = from(bucket: "EnergyMonitoring")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r[“_measurement”] == “DPM”)
  |> filter(fn: (r) => r[“Device”] == “DPM_TNB_MainIncoming”)
  |> filter(fn: (r) => r[“Location”] == “MSB_ROOM_1”)
  |> filter(fn: (r) => r[“_field”] == “Total kWh”)
  |> first()
  |> yield(name: "First")

Last = from(bucket: "EnergyMonitoring")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r[“_measurement”] == “DPM”)
  |> filter(fn: (r) => r[“Device”] == “DPM_TNB_MainIncoming”)
  |> filter(fn: (r) => r[“Location”] == “MSB_ROOM_1”)
  |> filter(fn: (r) => r[“_field”] == “Total kWh”)
  |> last()
  |> yield(name: "Last")

union(tables: [First, Last])
|> difference()
|> map(fn: (r) => ({r with delta_kWh_squared: r._value * r_value}))

// Now here comes the part I am pretty unsure how to do //

|> map(fn: (r) => ({r with _value: delta_kVArh_squared + delta_kWh_squared}))
|> map(fn: (r) => ({r with Power_Factor: math.sqrt(x: r._value)}))
8

Hi @grant1 ,

Thanks for the valuable input.

I have manage to solve the issue with the following code:

import "math"


from(bucket: "EnergyMonitoring")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "DPM" and r.Device == "DPM_TNB_MainIncoming" and
    (r._field == "Total_kVArh" or r._field == "Total_kWh")
  )


 |> spread()


|> pivot(rowKey:["_measurement"], columnKey: ["_field"], valueColumn: "_value")
    |> map(fn: (r) => ({
    r with
    PF: (r.Total_kWh/(math.sqrt(x:((r.Total_kWh * r.Total_kWh)  + (r.Total_kVArh * r.Total_kVArh)))))
    
   }))

    |> drop(columns: ["Total_kVArh", "Total_kWh"])

Will try out your method and find out how it goes.