Canvas days calculations

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

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?

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

@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"
}

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?

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.

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"}

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

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

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"}

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

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

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

Thanks, that works

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.