Calculate percentages with use of identifier tag

Fluxlang
1

Hello,

I’m trying to (eventually) calculate a percentage using the following query :

from(bucket: "kgnv_rd")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id"  or r._field == "Totals_Value" ))
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> filter(fn: (r) => r.Totals_Id == 63 or r.Totals_Id == 64  )

  |> map(fn: (r) => ({r with _totalwork: if r.Totals_Id == 63 or r.Totals_Id == 98 then r.Totals_Value/1000.0 else 0.0}))
  |> map(fn: (r) => ({r with _totaltransport: if r.Totals_Id == 64 or r.Totals_Id == 98 then r.Totals_Value/1000.0 else 0.0}))
  //|> map(fn: (r) => ({r with _percentagework: r._totaltransport+r._totalwork}))
  |> drop(columns: ["Totals_Value"])

This will result in the following output ;

image
image1496×267 19.6 KB

What I need is to calculate the percentage between _totaltransport and _totalwork, which are based upon the identifier Totals_id ( 63 / 64 ). Actually the values from 63/64 need to be in the same row to be able to calculate, but i don’t now how I can allign them properly?

I’ve been searching/trying for hours but i’am unable to solve this unfortunately.

Is there anybody who can help me in the right directions?

Thanks,

2

Hi @bart1992,
Welcome to the community. Are you happy to export a snippet of your data? Hopefully, I can then help you out from there.

Thanks :slight_smile:

3

@bart1992 I’ve had to make some assumptions about your data based on your query, but I think this will work for you:

data = from(bucket: "kgnv_rd")
    |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id"  or r._field == "Totals_Value" ))
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

totalTransport = data
    |> filter(fn: (r) => r.Totals_Id == "64")
    |> map(fn: (r) => ({r with _field: "_totaltransport", _value: r.Totals_Value/1000.0 }))

totalWork = data
    |> filter(fn: (r) => r.Totals_Id == "63")
    |> map(fn: (r) => ({r with _field: "_totalwork", _value: r.Totals_Value/1000.0 }))

union(tables: [totalTransport, totalWork])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") 
    |> map(fn: (r) => ({ r with _value: r._totaltransport / r._totalwork }))
4

@scott @Jay_Clifford many thanks for your responses.

Managed to get it working based upon your example @Jay_Clifford

    |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "${DeviceID}" and (r._field == "Totals_Id"  or r._field == "Totals_Value" ))
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

totalTransport = data
    |> filter(fn: (r) => r.Totals_Id == 64)  
    |> map(fn: (r) => ({r with _field: "totaltransport", _value: r.Totals_Value/1000.0 }))

totalWork = data
    |> filter(fn: (r) => r.Totals_Id == 63)
    |> map(fn: (r) => ({r with _field: "totalwork", _value: r.Totals_Value/1000.0 }))

union(tables: [totalTransport, totalWork])
    |> truncateTimeColumn(unit: 10m)
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") 
    |> map(fn: (r) => ({ r with PercentageWork: (r.totaltransport / r.totalwork)  })) 
    |> map(fn: (r) => ({ r with PercentageTransport: 100.0 - r.PercentageWork  }))    
|> drop(columns: ["totaltransport", "totalwork"])
|> last(column: "PercentageTransport")

Had to use the ‘truncateTimeColumn’ function to be able to align the row’s from Transport/Work since there was a slight ( 10 ms ) time difference between the datapoints ( vehicle CANbus data ).

Again, thanks a lot!

5

That was all @scott :slight_smile: glad you got it working.

1 Like