v 8.17.4
In my metric visualisation I would like to show the sum of NumberOfPeople who attended clinics with a uniqueClinicName .
Is this possible? I can see how I can get sum(NumberOfPeople) and unique_count(ClinicName).
But is it possible to get the the sum(NumberOfPeople) who attended unique_count(ClinicName) ?
Can you give a sample data set and result?
Like
Stephen clinic A
Stephen clinic B
Robin Clinic C
Dave Clinic A
Dave Clinic D
What is you desired result?
Thanks for your reply Stephen.
ClinicName's will always have the same NumberOfPeople, so if I can select all unique ClinicName's and then sum the NumberOfPeople of each ClinicName, I will have a total of: 9
| ClinicName | NumberOfPeople |
|---|---|
| Robin | 2 |
| Robin | 2 |
| Robin | 2 |
| Dave | 3 |
| Stephen | 4 |
| Stephen | 4 |
Is there a timestamp with the data then could probably use ther latest function on each ClinicName
Thanks for your reply @stephenb.
I can't find the latest function in the es|ql docs, or anywhere else.
Could you please elaborate on how I can do this, or at least point me in the right direction?
Apologies in Lens it is Last Value
Do you have a timestamp on the data?
And how many clinics are there? (There is a limit on aggs
We can build a metric if you have a timestamp, some way to get the last value
@stephenb thanks for your reply.
Yes we have a date-time field on the index called CreatedDate and at the moment there's only 6000 records.
Just to restate my goal, Sum NumberOfPeople where ClinicName is distinct.
Total would be: 9
| ClinicName | NumberOfPeople | CreatedDate |
|---|---|---|
| Robin | 2 | 2023-08-26 10:51:54 |
| Robin | 2 | 2023-08-25 10:41:54 |
| Robin | 2 | 2023-08-24 10:31:54 |
| Dave | 3 | 2023-08-23 10:21:54 |
| Stephen | 4 | 2023-02-26 12:51:54 |
| Stephen | 4 | 2023-01-26 10:51:54 |
Hi @Robin_Gorry
Here is the data I used
PUT /clinics
{
"mappings": {
"properties": {
"ClinicName": {
"type": "keyword"
},
"NumberOfPeople": {
"type": "integer"
},
"CreatedDate": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss"
}
}
}
}
POST /clinics/_bulk
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-26 10:51:54" }
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-25 10:41:54" }
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-24 10:31:54" }
{ "index": {} }
{ "ClinicName": "Dave", "NumberOfPeople": 3, "CreatedDate": "2023-08-23 10:21:54" }
{ "index": {} }
{ "ClinicName": "Stephen", "NumberOfPeople": 4, "CreatedDate": "2023-02-26 12:51:54" }
{ "index": {} }
{ "ClinicName": "Stephen", "NumberOfPeople": 4, "CreatedDate": "2023-01-26 10:51:54" }
POST /_query/async?drop_null_columns&format=txt
{
"query": "FROM clinics | STATS max_num_people = MAX(NumberOfPeople) BY ClinicName | STATS total_people = SUM(max_num_people)"
}
# Result
#! No limit defined, adding default limit of [1000]
total_people
---------------
9
Or run the first 2 commands and then go to discover ...
FROM clinics
| STATS max_num_people = MAX(NumberOfPeople) BY ClinicName
| STATS total_people = SUM(max_num_people)
Technically that just takes the sum of the max which may work.
In Lens you will need to create a data View with
Then Create a Lens Metrics
We will get the last value of NumberOfPeople for each Clinic Name and then Sum Them
Primary Metrics
Last Value : NumberOfPeople
Then Breakdown by Top Value ClinicName
1000
Then Collapse by Sum
Hope This Helps
Thank you again @stephenb for this great response.
I was very close to getting it right but I needed the MAX(NumberOfPeople).
I've learnt quite a few things from your full response, much appreciated.
1 Like