Sort bar chart to display highest values on the top

Grafana Dashboards
1

Hi all,

I am building an electrical consumption dashboard and ended up with a hourly kWh usage per switch.

it displays OK :

image
image1920×937 147 KB

But I’d like to sort this bar chart, so the switches that used the most kWh are displayed at the top, and descending. I tried to play with the sort feature but couldn’t get this sorted. Can someone help me here ?

here is the Flux query I’m currently using :

from(bucket: "homeassistant")
  |> range(start: -24h)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "cuisine_toaster_etc_daily" or r["entity_id"] == "cave_boucle_sanitaire_daily" or r["entity_id"] == "cave_congelateur_etc_daily" or r["entity_id"] == "cave_chaudiere_daily" or r["entity_id"] == "cave_seche_linge_prise_meross_daily" or r["entity_id"] == "cave_modems_serveur_unraid_et_co_daily" or r["entity_id"] == "cave_seche_linge_prise_meross_daily" or r["entity_id"] == "guestroom_consoles_etc_daily" or r["entity_id"] == "guestroom_projecteur_daily" or r["entity_id"] == "chambre_raph_et_cel_tv_etc_daily" or r["entity_id"] == "chambre_raph_et_cel_tables_de_nuit_daily" or r["entity_id"] == "cuisine_fours_daily" or r["entity_id"] == "cuisine_frigo_daily" or r["entity_id"] == "cuisine_lave_vaisselle_daily" or r["entity_id"] == "cuisine_machine_cafe_etc_daily" or r["entity_id"] == "cuisine_tablette_modem_rpi_plexamp_etc_daily" or r["entity_id"] == "salle_a_manger_alexa_daily" or r["entity_id"] == "salle_a_manger_arcade_daily" or r["entity_id"] == "salle_a_manger_lampe_varmblixt_ikea_halo_daily" or r["entity_id"] == "salle_a_manger_prise_humidificateur_derriere_siege_emmanuel_daily" or r["entity_id"] == "salle_a_manger_rpi_plexamp_salle_a_manger_daily" or r["entity_id"] == "salle_de_bain_enceinte_petite_lampe_etc_daily" or r["entity_id"] == "salle_de_bain_radiateur_elec_daily" or r["entity_id"] == "salon_aspi_cendres_daily" or r["entity_id"] == "salon_barre_son_etc_daily" or r["entity_id"] == "salon_prise_logia_lampe_togo_daily" or r["entity_id"] == "salon_tv_etc_daily" or r["entity_id"] == "lampes_consommation_globale_daily")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "last")

thanks ! :slight_smile:

2

Someone to help me understand how to sort these values ?

3

hi @raph1 :wave:

have you tried the ‘sort by’ transformation ?

see a similar post here

4

Or try adding the sort() function before the yield() function and see how that changes your graph.

5

Hello,

I’ve tried this earlier, but for some reason:

image
image1410×780 162 KB

So it looks like I cannot sort stuff on several single values ?

6

I tried this but cannot figure out the exact query.

It ends up displaying no data each time I try to modify it.

here is my full query :

from(bucket: "homeassistant")
  |> range(start: -24h)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "cuisine_toaster_etc_monthly" or r["entity_id"] == "cave_boucle_sanitaire_monthly" or r["entity_id"] == "cave_congelateur_etc_monthly" or r["entity_id"] == "cave_chaudiere_monthly" or r["entity_id"] == "meross_seche_linge_monthly" or r["entity_id"] == "cave_modems_serveur_unraid_et_co_monthly" or r["entity_id"] == "cave_seche_linge_prise_meross_monthly" or r["entity_id"] == "guestroom_consoles_etc_monthly" or r["entity_id"] == "guestroom_projecteur_monthly" or r["entity_id"] == "chambre_raph_et_cel_tv_etc_monthly" or r["entity_id"] == "chambre_raph_et_cel_tables_de_nuit_monthly" or r["entity_id"] == "cuisine_fours_monthly" or r["entity_id"] == "cuisine_frigo_monthly" or r["entity_id"] == "cuisine_lave_vaisselle_monthly" or r["entity_id"] == "cuisine_machine_cafe_etc_monthly" or r["entity_id"] == "cuisine_tablette_modem_rpi_plexamp_etc_monthly" or r["entity_id"] == "salle_a_manger_alexa_monthly" or r["entity_id"] == "salle_a_manger_arcade_monthly" or r["entity_id"] == "salle_a_manger_lampe_varmblixt_ikea_halo_monthly" or r["entity_id"] == "salle_a_manger_prise_humidificateur_derriere_siege_emmanuel_monthly" or r["entity_id"] == "salle_a_manger_rpi_plexamp_salle_a_manger_monthly" or r["entity_id"] == "salle_de_bain_enceinte_petite_lampe_etc_monthly" or r["entity_id"] == "salle_de_bain_radiateur_elec_monthly" or r["entity_id"] == "salon_aspi_cendres_monthly" or r["entity_id"] == "salon_barre_son_etc_monthly" or r["entity_id"] == "salon_prise_logia_lampe_togo_monthly" or r["entity_id"] == "salon_tv_etc_monthly" or r["entity_id"] == "lampes_consommation_globale_monthly")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "last")

I’ve tried to simply add |> sort() one line above yield. But it is not changing anything.

7

Hi @raph1

Can you put your query (with the sort() function) in Influx Data Explorer and toggle the “View Raw Data” switch to see a few rows of data?

I tested this on some of my data and simply inserting this function

|> sort(columns: ["_value"], desc: false)

sorts the _value field.

8

well, instead of sorting thing, it displays all me sensors values twice.

here is the query I used :

from(bucket: "homeassistant")
  |> range(start: -24h)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["entity_id"] == "cuisine_toaster_etc_monthly" or r["entity_id"] == "cave_boucle_sanitaire_monthly" or r["entity_id"] == "cave_congelateur_etc_monthly" or r["entity_id"] == "cave_chaudiere_monthly" or r["entity_id"] == "meross_seche_linge_monthly" or r["entity_id"] == "cave_modems_serveur_unraid_et_co_monthly" or r["entity_id"] == "cave_seche_linge_prise_meross_monthly" or r["entity_id"] == "guestroom_consoles_etc_monthly" or r["entity_id"] == "guestroom_projecteur_monthly" or r["entity_id"] == "chambre_raph_et_cel_tv_etc_monthly" or r["entity_id"] == "chambre_raph_et_cel_tables_de_nuit_monthly" or r["entity_id"] == "cuisine_fours_monthly" or r["entity_id"] == "cuisine_frigo_monthly" or r["entity_id"] == "cuisine_lave_vaisselle_monthly" or r["entity_id"] == "cuisine_machine_cafe_etc_monthly" or r["entity_id"] == "cuisine_tablette_modem_rpi_plexamp_etc_monthly" or r["entity_id"] == "salle_a_manger_alexa_monthly" or r["entity_id"] == "salle_a_manger_arcade_monthly" or r["entity_id"] == "salle_a_manger_lampe_varmblixt_ikea_halo_monthly" or r["entity_id"] == "salle_a_manger_prise_humidificateur_derriere_siege_emmanuel_monthly" or r["entity_id"] == "salle_a_manger_rpi_plexamp_salle_a_manger_monthly" or r["entity_id"] == "salle_de_bain_enceinte_petite_lampe_etc_monthly" or r["entity_id"] == "salle_de_bain_radiateur_elec_monthly" or r["entity_id"] == "salon_aspi_cendres_monthly" or r["entity_id"] == "salon_barre_son_etc_monthly" or r["entity_id"] == "salon_prise_logia_lampe_togo_monthly" or r["entity_id"] == "salon_tv_etc_monthly" or r["entity_id"] == "lampes_consommation_globale_monthly")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "last")
  |> sort(columns: ["_value"], desc: false)

As you can see :

image
image1920×970 166 KB

9

How can we restrict with top 10/ top 20 values/ ticks to be displayed?

10

Have you experimented with the Limit transformation?

image
image563×197 8.86 KB

Use this transformation to restrict the number of rows displayed, providing a more focused view of your data.

Below is an example illustrating the impact of the Limit transformation on a response from a data source:

Time Metric Value
2020-07-07 11:34:20 Temperature 25
2020-07-07 11:34:20 Humidity 22
2020-07-07 10:32:20 Humidity 29
2020-07-07 10:31:22 Temperature 22
2020-07-07 09:30:57 Humidity 33
2020-07-07 09:30:05 Temperature 19

Here is the result after adding a Limit transformation with a value of ‘3’:

Time Metric Value
2020-07-07 11:34:20 Temperature 25
2020-07-07 11:34:20 Humidity 22
2020-07-07 10:32:20 Humidity 29

Using a negative number, you can keep values from the end of the set. Here is the result after adding a Limit transformation with a value of ‘-3’:

Time Metric Value
2020-07-07 10:31:22 Temperature 22
2020-07-07 09:30:57 Humidity 33
2020-07-07 09:30:05 Temperature 19

You may need to use the Sort by transformation as well to get the desired results.