v 8.17.4
I have a question about a specific use case, but I think it speaks to a broader principle of how Kibana works.
I want to use a Metric visualisation panel to display the number of unique Clinics within the global filter date range as the primary metric .e.g. unique_count(ClinicName), works perfectly.
As a secondary metric, I would like to display the percentage that the primary number represents in the total number of unique Clinics, so all clinics over all time.
Can I use a formula or the Advanced filter to do this?
I have tried many different ways to do this but have failed.
Or is everything that happens within a dashboard bound to the global date parameters?
Unfortunately there's no option to escape the time range with Lens formula.
You could approach this type of calculation using a ES|QL query, with something like the following:
FROM index
| STATS
numerator = COUNT_DISTINCT( ClinicName) WHERE @timestamp >= ?_tstart and @timestamp <= ?_tend,
denominator = COUNT_DISTINCT(ClinicName)
| EVAL ratio = numerator / denominator
| KEEP ratio
Thank you Marco, that query has got me most of the way there and taught me a lot.
It seems like the query wants to force a table visualisation, but I'm wanting a Metric.
There is an error with the Break down by field and when I delete the missing field, the visualisation comes back but reverts back to the table when I add it to the Dashboard, even when I've saved it.
Also there is any way to change the field label "numerator" as there is in the lens vue e.g. from numerator to Number of clinics?
It is possible to create the ES|QL panel on a dashboard directly, no need to pass thru Discover.
As per the dimension label, changing the name should apply here:
In Kibana, all visualizations on a dashboard are bound to the global date filter by default this includes Metric panels and formulas. If you want to compare a time-filtered value (e.g. unique clinics within the current range) with an all-time value (e.g. total unique clinics), a standard Metric visualization does not allow for this comparison to be made.
You can achieve this using TSVB (Time Series Visual Builder). TSVB enables users to set non-global time ranges for particular series which allows for more complex comparisons so that users can show both filtered and unfiltered values and compute the difference alongside the percentage.
While standard visualizations do not allow this flexibility, you can rely on TSVB for this kind of comparison.
Thanks Marco.
I created the a new visualisation from the dashboard, and the display saves as a Metric, which is great.
The option is still not there to re-label the numerator field, so for now I've called the field Clinic, but it would be nice to be able to label it Number of clinics.
As last resort it is always possible to relabel it in ES|QL itself (with a RENAME command)
FROM index
| STATS
numerator = COUNT_DISTINCT( ClinicName) WHERE @timestamp >= ?_tstart and @timestamp <= ?_tend,
denominator = COUNT_DISTINCT(ClinicName)
| EVAL ratio = numerator / denominator
| KEEP numerator, ratio
| RENAME numerator as `Number of clinics`
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.
