# Calculate percentages with use of identifier tag

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 ;

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,

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 ``````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 }))
``````

@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!

That was all @scott glad you got it working.

1 Like