Is anyone can provide me total gain and loss efficient way of query

InfluxDB 2
, ,
1

i dont know how can we calculate gain and loss as well as total in single query is it possible ? but i want to fetch every 5sec as well what will be load impact on drive ?

2

Hi,

t1=from(bucket: “sample”)
|> range(start:-5s)
|> filter(fn: (r) => r[“_measurement”] == “state”)
|> filter(fn: (r) => r[“state”] == “AP”)
|> filter(fn: (r) => r[“Item”] == “Cotton”)
|> filter(fn: (r) => r[“tag”] == “sale” )
|> last()
|> sum()
t2=from(bucket: “sample”)
|> range(start:-5s)
|> filter(fn: (r) => r[“_measurement”] == “state”)
|> filter(fn: (r) => r[“state”] == “AP”)
|> filter(fn: (r) => r[“plant”] == “Cotton”)
|> filter(fn: (r) => r[“tag”] == “revenue”)
|> last()
|> drop(columns:[“_measurement”,“_field”,“_stop”,“tag”,“_start”])
|> mean()
join(tables: {t1: t1, t2: t2}, on: [“state”,“item”])
|> map(fn: (r) => ({r with _value: (r._value_t1+r._value_t2)}))
|> map(fn: (r) => ({r with _value: (r._value_t1-r._value_t2)}))
|>group()
|>rename(columns: {_value_t1:“sale”,_value_t2:“revenue”,_value:“profit”})
|>rename(columns: {_value_t1:“sale”,_value_t2:“revenue”,_value:“loss”})

Keep the time range for 5 sec

1 Like
3

actually what i want is hourly daily monthly and total then i want hourly -total,gain,loss , daily-total,gain,loss something like this

from(bucket: "Neo")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "async_m")
  |> filter(fn: (r) => r["_field"] == "current")
  |> filter(fn: (r) => r["location"] == "mohsin")
  |> aggregateWindow(every: 1h, fn: sum, createEmpty: false)
  |> spread()
  |> yield(name: "sum")
4

Hi,

Then you have to keep the date range filter at the top of the report and select hourly, weekly, daily etc. and keep the time range as below

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)

6

[quote=“AVVS_Sudheer, post:2, topic:32658”]
t1=from(bucket: “sample”)
|> range(start:-5s)
|> filter(fn: (r) => r[“_measurement”] == “state”)
|> filter(fn: (r) => r[“state”] == “AP”)
|> filter(fn: (r) => r[“Item”] == “Cotton”)
|> filter(fn: (r) => r[“tag”] == “sale” )
|> last()
|> sum()
t2=from(bucket: “sample”)
|> range(start:-5s)
|> filter(fn: (r) => r[“_measurement”] == “state”)
|> filter(fn: (r) => r[“state”] == “AP”)
|> filter(fn: (r) => r[“plant”] == “Cotton”)
|> filter(fn: (r) => r[“tag”] == “revenue”)
|> last()
|> drop(columns:[“_measurement”,“_field”,“_stop”,“tag”,“_start”])
|> mean()
join(tables: {t1: t1, t2: t2}, on: [“state”,“item”])
|> map(fn: (r) => ({r with _value: (r._value_t1+r._value_t2)}))
|> map(fn: (r) => ({r with _value: (r._value_t1-r._value_t2)}))
|>group()
|>rename(columns: {_value_t1:“sale”,_value_t2:“revenue”,_value:“profit”})
|>rename(columns: {_value_t1:“sale”,_value_t2:“revenue”,_value:“loss”})

Keep the time range for 5 sec

In the above query revenue+sales=Profit, revenue-sales=Loss
say revenue=1000 and sales=2000 then profit=3000 loss= 1000-2000=-1000

1 Like
8

image
image981Ă—213 15.4 KB

it working thnx alot bro @AVVS_Sudheer

9

so this provide join is limited by 2 parent how can we have more then 4row instead 1 row

joined_data = join(tables: {t1: t1, t2: t2}, on: ["_stuff", "stuff"])
  |> map(fn: (r) => ({ r with _value: r._value_t1 - r._value_t2 }))

joined_data2 how we do this since join only limit by 2 const 
 t3 and t4 table more append how ?
10

Hi haideraf,

You can use the below way

t5=t1+t2
t6=t3+t4

only the tags or columns which are required in the report, you can keep. remaining tags you can drop.

1 Like