# Canvas days calculations

**URL:** <https://discuss.elastic.co/t/canvas-days-calculations/172889>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [March 19, 2019, 3:31am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889 "2019-03-19T03:31:05Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 19, 2019, 3:31am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/1 "2019-03-19T03:31:05Z")

</div>

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](https://us1.discourse-cdn.com/elastic/original/3X/0/b/0b732e37b5c239c167e7599fcef2d494e9c47dbc.png)

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?

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [March 20, 2019, 5:21pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/2 "2019-03-20T17:21:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 21, 2019, 12:25am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/3 "2019-03-21T00:25:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [March 25, 2019, 5:08pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/4 "2019-03-25T17:08:29Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 26, 2019, 12:47am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/5 "2019-03-26T00:47:19Z")

</div>

> [@lukas](#):
>
> omething 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.

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [March 26, 2019, 4:14pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/6 "2019-03-26T16:14:03Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 27, 2019, 2:47am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/7 "2019-03-27T02:47:35Z")

</div>

> [@lukas](#):
>
> 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?

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 28, 2019, 12:03am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/8 "2019-03-28T00:03:48Z")

</div>

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 the`formatnumber "0"` it works

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

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [March 28, 2019, 4:22pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/9 "2019-03-28T16:22:16Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [March 28, 2019, 11:58pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/10 "2019-03-28T23:58:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [April 24, 2019, 11:10am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/11 "2019-04-24T11:10:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lukas](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas/32/6812_2.png) [@lukas](https://discuss.elastic.co/u/lukas)\
**Post date:** [April 24, 2019, 9:40pm UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/12 "2019-04-24T21:40:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hashitha](https://avatars.discourse-cdn.com/v4/letter/h/ce73a5/32.png) [@hashitha](https://discuss.elastic.co/u/hashitha)\
**Post date:** [April 25, 2019, 5:09am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/13 "2019-04-25T05:09:38Z")

</div>

Thanks, that works

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 23, 2019, 5:09am UTC](https://discuss.elastic.co/t/canvas-days-calculations/172889/14 "2019-05-23T05:09:40Z")

</div>

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