FluxQL query with percentage calculation for table with time on columns

[agonzalez]

Hi to all…

I have a measurement who stores data with counters for some categories and a time range, like:

selectGPT | Categoria | Fecha | Hora | Minutos | bloque_hora | value
Category1 | RECIBIDAS | 31/07/2023 | 00 | 00 | 00:00 | 12
Category1 | ATENDIDAS | 31/07/2023 | 00 | 00 | 00:00 | 8
Category1 | RECIBIDAS | 31/07/2023 | 00 | 15 | 00:00 | 23
Category1 | ATENDIDAS | 31/07/2023 | 00 | 15 | 00:00 | 20
Category2 | RECIBIDAS | 31/07/2023 | 00 | 00 | 00:00 | 8
Category2 | ATENDIDAS | 31/07/2023 | 00 | 00 | 00:00 | 5
Category2 | RECIBIDAS | 31/07/2023 | 00 | 15 | 00:00 | 4
Category2 | ATENDIDAS | 31/07/2023 | 00 | 15 | 00:00 | 4

Y need to create a table to show percentage: ATENDIDAS/RECIBIDAS (Attended/Received) with hour on columns.

I have archieved this FluxQL to show with categoria = ATENDIDAS:

from(bucket: “BUSINESS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “Callcenter.Llamadas” and r._field == “value” and (r.categoria == “ATENDIDAS”))
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> group(columns: [“hora”, “minutos”, “bloque_hora”, “categoria”, “_field”])
|> pivot(rowKey:[“hora”, “minutos”, “selectGPT”, “selectGPT”], columnKey: [“bloque_hora”], valueColumn: “_value”)

This with transforms Merge, Reduce row to total (for all bloque_hora tags) and organize fields (to order columns only) shows a table like:

selectGPT | Total | 00:00 | 00:15 | 00:30 | … | 23:45 |
Category1 | 123 | 12 | 23 | 19 | … | 24 |
Category2 | 218 | 33 | 54 | 21 | … | 26 |
Category3 | 54 | 7 | 23 | 0 | … | 35 |

Y need to show like that the ratio between two “categoria” tags: RECIBIDAS and ATENTIDAS. But I don’t know how to archieve that.

I tried to do something like that:

recibidas=from(bucket: “BUSINESS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “Callcenter.Llamadas” and r._field == “value” and (r.categoria == “RECIBIDAS”))
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> group(columns: [“hora”, “minutos”, “categoria”, “_field”])
|> pivot(rowKey: [“hora”, “minutos”, “selectGPT”, “selectGPT”], columnKey: [“bloque_hora”], valueColumn: “_value”)

atendidas=from(bucket: “BUSINESS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “Callcenter.Llamadas” and r._field == “value” and (r.categoria == “ATENDIDAS”))
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> group(columns: [“hora”, “minutos”, “categoria”, “_field”])
|> pivot(rowKey: [“hora”, “minutos”, “selectGPT”, “selectGPT”], columnKey: [“bloque_hora”], valueColumn: “_value”)

But don’t know how to join all buckets and map a atendidas/recibidas to show a percentage between two values for same “selectGPT” and “categoria” field showing like table above. Like this:

selectGPT | Total | 00:00 | 00:15 | 00:30 | … | 23:45 |
Category1 | 123 | 0.5 | 0.86 | 0.84 | … | 0.78 |
Category2 | 218 | 0.23 | 0.33 | 0.44 | … | 0.94 |
Category3 | 54 | 0.84 | 0.93 | 0.3 | … | 0.99 |

How can archieve that?

[agonzalez]

Finally found how to do. After two days dealing with that the query is like this:

recibidas=from(bucket: “BUSINESS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “Callcenter.Llamadas” and r._field == “value” and (r.categoria == “RECIBIDAS_INCL_TRANSFERENCIAS”) )
|> filter(fn: (r) => r[“selectGPT”] =~ /^${grupo:regex}$/)
|> filter(fn: (r) => r[“_value”] > 0)
|> aggregateWindow(every: v.windowPeriod, fn: sum, createEmpty: false)
|> group(columns: [ “fecha”, “categoria”, “selectGPT”, “_field”])

atendidas=from(bucket: “BUSINESS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “Callcenter.Llamadas” and r._field == “value” and (r.categoria == “ATENDIDAS_INCL_TRANSFERENCIAS”))
|> aggregateWindow(every: v.windowPeriod, fn: sum, createEmpty: false)
|> group(columns: [ “fecha”, “categoria”, “selectGPT”, “_field”])

join(tables: {atendidas: atendidas, recibidas: recibidas}, on: [“hora”, “minutos”, “bloque_hora”, “selectGPT”, “_field”])
|> map(fn: (r) => ({ r with _value: r._value_atendidas / r._value_recibidas }))
|> group(columns: [“hora”, “minutos”, “bloque_hora”, “categoria”, “_field”])
|> pivot(rowKey:[“selectGPT”], columnKey: [“bloque_hora”], valueColumn: “_value”)

After that used 3 Transforms:

1 Merge
2. Add field from calculation to reduce row to “mean”
3. Organize fields to sort columns

And some overrides to show data with colours…

[Anaisdg]

@agonzalez
Thank you so much for sharing your solution with the community!