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

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 ?

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

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)
|> yield(name: "sum")
``````

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)

[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

it working thnx alot bro @AVVS_Sudheer

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 ?

``````

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