Canvas days calculations

Elastic Stack Kibana
1

I have a table with the following headers
AppointmentId, BookedByUser, BookedDate, Status

I have managed to create a horizontal bar chart using the following sql

SELECT COUNT(AppointmentId) as Appointments,  BookedByUser as "Booked by"
FROM "appointments" WHERE Status='BOOKED' GROUP BY BookedByUser

image
image.png665×976 18.3 KB

Now I want to display the average number of days each user has worked (not the total number of appointments made by a user divide by the time range). e.g. If a user has made 4 appointments over 2 dates then the average for that user is 2.

Even if I can show this data on a separate table that is fine. Something like

|BookedByUser|TotalAppointmentsBooked|AVG Days worked

Is this possible with the current canvas implementations?

2

Yeah, it's probably possible. Could you provide an example of what your data looks like? Including your time field.

3

@lukas thanks
This is the mapping and the data

PUT appointments
{
  "settings": {
    "number_of_shards": "1",
    "number_of_replicas": "1"
  },
  "mappings": {
    "appointment": {
      "properties": {
        "AppointmentId": {
          "type": "long"
        },
        "@timestamp": {
          "type": "date"
        },
        "BookedByUser": {
          "properties": {
            "FirstName": {
              "type": "keyword"
            },
            "LastName": {
              "type": "keyword"
            },
            "Username": {
              "type": "keyword"
            },
            "FullName": {
              "type": "keyword"
            }
          }
        },
        "AppointmentDate": {
          "type": "date",
          "format": "dd-MM-yyyy"
        },
        "Status": {
          "type": "keyword"
        }
      }
    }
  }
}

PUT appointments/appointment/1001
{
  "AppointmentId": 1001,
  "@timestamp": "2019-03-20T15:00",
  "BookedByUser": {
    "Username": "john@doe.com",
    "FirstName": "John",
    "FullName": "John Doe",
    "LastName": "Doe"
  },
  "AppointmentDate": "20-03-2019",
  "Status": "BOOKED"
}

PUT appointments/appointment/1002
{
  "AppointmentId": 1002,
  "@timestamp": "2019-03-22T15:00",
  "BookedByUser": {
    "Username": "john@doe.com",
    "FirstName": "John",
    "FullName": "John Doe",
    "LastName": "Doe"
  },
  "AppointmentDate": "23-02-2019",
  "Status": "BOOKED"
}

PUT appointments/appointment/1003
{
  "AppointmentId": 1003,
  "@timestamp": "2019-03-23T15:00",
  "BookedByUser": {
    "Username": "john@doe.com",
    "FirstName": "John",
    "FullName": "John Doe",
    "LastName": "Doe"
  },
  "AppointmentDate": "23-03-2019",
  "Status": "BOOKED"
}

PUT appointments/appointment/1004
{
  "AppointmentId": 1004,
  "@timestamp": "2019-03-23T15:00",
  "BookedByUser": {
    "Username": "jane@doe.com",
    "FirstName": "Jane",
    "FullName": "Jane Doe",
    "LastName": "Doe"
  },
  "AppointmentDate": "23-03-2019",
  "Status": "BOOKED"
}

PUT appointments/appointment/1004
{
  "AppointmentId": 1005,
  "@timestamp": "2019-03-25T15:00",
  "BookedByUser": {
    "Username": "jane@doe.com",
    "FirstName": "Jane",
    "FullName": "Jane Doe",
    "LastName": "Doe"
  },
  "AppointmentDate": "25-03-2019",
  "Status": "BOOKED"
}
4

I guess I'm still not understanding exactly what metric you're trying to calculate here... Are you trying to get the average number of appointments per day for a user, or just the number of days the user has worked?

For example, if a user has made 8 appointments over 2 days, are you wanting the number of days worked (2) or the average number of appointments per day (4), or something else?

5

Sorry, it is a bit confusing.

  1. I want to get the number of days worked (2) per user
  2. Calculate average appointments booked using the number of days worked. i.e. total appointments booked (8)/number of days worked (2)=4

e.g. if we take a range of 30 days and we have the following data

  • User A made a total of 10 appointments over 4 days
  • User B made a total of 4 appointments over 2 days
  • User C made a total of 80 appointments over 20 days

I would like a table like this

User Days worked Total Apps Avg Apps
User C 20 80 4
User A 4 10 2.5
User B 2 4 2

Basically, our customers have part-time or casual employees so they want to know how they perform.

6

This might not be the best way to accomplish it but it will probably work. You can copy/paste this into the expression input:

filters
| essql 
  query="SELECT BookedByUser, HISTOGRAM(BookedDate, INTERVAL 1 DAY) AS BookedDate, COUNT(AppointmentId) AS Appointments
FROM \"appointments\"
GROUP BY BookedByUser, BookedDate"
| ply by="BookedByUser" fn={math "count(BookedDate)" | as "DaysWorked"} fn={math "sum(Appointments)" | as "TotalApps"}
| mapColumn "AvgApps" fn={math "TotalApps/DaysWorked"}
7

Thanks @lukas this works great. Is it possible to order the table by AvgApps?

8

I have managed to sort it by using sort AvgApps
However, it doesn't work if there is a formatnumber

| mapColumn "AvgApps" fn={math "TotalApps/DaysWorked" | formatnumber "0"}
| sort AvgApps

if you remove theformatnumber "0" it works

I have mentioned this in github issue https://github.com/elastic/kibana/issues/27926

9

Yeah, that's actually expected since formatting the number changes it from a number to a string, and strings/numbers are sorted differently. (For strings, "10" comes before "5", similar to how "Alpha" comes before "Bee".)

All you need to do is change where you're formatting it:

| mapColumn "AvgApps" fn={math "TotalApps/DaysWorked"}
| sort AvgApps
| mapColumn "AvgApps" fn={math "AvgApps" | formatnumber "0"}
10

I feel silly now. Thanks for all you help @lukas

11

I have got this working well except when I choose a range of more than 1 year the data displayed is incorrect. E.g if I choose Last year the data is all correct but when I choose last 5 years it doesn't show everything only shows one record. When I debug it seem to get only first 1000 records this could be the problem

12

Yeah, you're going to want to add count=10000 (or whatever limit you want to set) for the essql function.

13

Thanks, that works