# Working days - how to find **URL:** \ **Category:** Kibana\ **Created:** [May 3, 2023, 11:03am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404 "2023-05-03T11:03:03Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 3, 2023, 11:03am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/1 "2023-05-03T11:03:03Z")
Hi, I have a problem. I'm getting data from the api which lists the rooms that users have booked. Each room has set working days and hours. I need to make a table where the Average real resource usage will be calculated (the formula is `((100*sum(realDuration))/(max(resource.workingMinutesInDay)*unique_count()))` count of working days in week is saved in resource.countOfWorkingDayOneWeek But I don't know how to find out how many days were working days by timerange (some resources have working days set to Monday-Friday, some to Monday-Sunday, some to Wednesday-Friday) So I was wondering if anyone has encountered this before. ---
**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\ **Post date:** [May 3, 2023, 11:44am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/2 "2023-05-03T11:44:16Z")
Hi @TheyCallMeTrinity, Can you share your index mapping? It's difficult to tell without knowing the data structure, but it sounds like you want to do an [aggregation based on a date range.](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-daterange-aggregation.html) ---
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 3, 2023, 12:15pm UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/3 "2023-05-03T12:15:45Z")
```auto { "dapi_events": { "mappings": { "properties": { "action": { "properties": { "action": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "created": { "type": "date" }, "doneBy": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "area": { "properties": { "id": { "type": "long" }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "parentAreas": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "attendees": { "properties": { "email": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "responseStatus": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "createdDateTime": { "type": "date" }, "eventDifferences": { "properties": { "key": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "value": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "finishedAsPlanned": { "type": "boolean" }, "iCalUId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "importance": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "isAllDay": { "type": "boolean" }, "isCancelled": { "type": "boolean" }, "lastModifiedDateTime": { "type": "date" }, "onlineMeetingProvider": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "organizer": { "properties": { "email": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "responseStatus": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "plannedDuration": { "type": "long" }, "plannedEnd": { "type": "date" }, "plannedStart": { "type": "date" }, "realDuration": { "type": "long" }, "realEnd": { "type": "date" }, "realStart": { "type": "date" }, "resource": { "properties": { "baseUtcOffset": { "type": "long" }, "capacity": { "type": "long" }, "daysOfWeek": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "displayName": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "email": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "endTime": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "id": { "type": "long" }, "resourceTypeId": { "type": "long" }, "resourceTypeName": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "serverTypeId": { "type": "long" }, "startTime": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "supportsDaylightSavingTime": { "type": "boolean" }, "timeZoneInfo": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "username": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "workingMinutesInDay": { "type": "long" } } }, "resourceProperties": { "properties": { "dataTypeId": { "type": "long" }, "dataTypeName": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "defaultValueBoolean": { "type": "boolean" }, "defaultValueNumber": { "type": "long" }, "description": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "matchAll": { "type": "boolean" }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "resourcePropertyItems": { "properties": { "defaultValue": { "type": "boolean" }, "id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "resourceValue": { "type": "boolean" } } }, "resourceTypeIds": { "type": "long" }, "resourceValueBoolean": { "type": "boolean" }, "resourceValueNumber": { "type": "long" } } }, "responseStatus": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "sensitivity": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "seriesMasterId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "serviceAccount": { "properties": { "email": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "eventId": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "responseStatus": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "showAs": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "subject": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } } } } ``` ---
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 3, 2023, 12:22pm UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/4 "2023-05-03T12:22:30Z")
and i need table like this ![output](https://us1.discourse-cdn.com/elastic/original/3X/a/2/a238ece0034e87ae70e078639aad906021d4e165.jpeg) ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [May 4, 2023, 11:44pm UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/5 "2023-05-04T23:44:16Z")
> [@TheyCallMeTrinity](#): > > count of working days in week is saved in resource.countOfWorkingDayOneWeek That's not in your mapping? > [@TheyCallMeTrinity](#): > > ```auto > "type": "text", > "fields": { > "keyword": { > "type": "keyword", > "ignore_above": 256 > } > } > > ``` Also you are using a lot of this type of multi-field, which is probably a bit of a waste and you might want to clean it up. ---
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 5, 2023, 6:54am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/6 "2023-05-05T06:54:46Z")
In "daysOfWeek" I have saved the days Monday, Tuesday, Wednesday,.... And then via the scripted field I have the length of the array. And I need to find out if the user enters, for example, a time range of 90 days, how many of those days were working ---
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 17, 2023, 8:24am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/7 "2023-05-17T08:24:28Z")
I still don't have a solution. Is there anyone here who has solved a similar thing please? Alternatively, does anyone have experience with any paid advice? ---
**Author:** ![carly.richmond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carly.richmond/32/104935_2.png) [@carly.richmond](https://discuss.elastic.co/u/carly.richmond)\ **Post date:** [May 17, 2023, 11:33am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/8 "2023-05-17T11:33:43Z")
I've not done anything similar to this but I'll give it a try. I'm assuming you're looking for some help on how to calculate the number of days based on the list of days from the `daysOfWeek` field? I think you need to do a rather involved script, [similar to this example for C#](https://stackoverflow.com/questions/1617049/calculate-the-number-of-business-days-between-two-dates) where you calculate the number of each of those days, making use of the [Painless DaysOfWeek functionality](https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-api-reference-shared-java-time.html) similar to the `java.time` package mirrored in Painless. Personally it's probably something I would calculate in a script, or even making use of a calendar capability to make sure I covered bank holidays, which I'm not sure from your mapping above you have access to in your document set. Hopefully that gives you an idea of where to start! ---
**Author:** ![TheyCallMeTrinity](https://avatars.discourse-cdn.com/v4/letter/t/50afbb/32.png) [@TheyCallMeTrinity](https://discuss.elastic.co/u/TheyCallMeTrinity)\ **Post date:** [May 17, 2023, 11:54am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/9 "2023-05-17T11:54:04Z")
I have the number of working days available for each resource. And I need to calculate the Average resource usage based on the selected timerange (30 days, a week, 3 months, whichever the user chooses) - the formula should look something like this ((100\*sum(realDuration))/(max(resource.workingMinutesInDay)\*unique\_count(TotalCountOfWorkingDay))) but I have no idea how to get to the TotalCountOfWorkingDay. What is the way to take something like params.\_interval in lens in TBSV and then pull only working days from that params.\_interval ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/5/4524072a47adb0cd56b3475d8c4e570e23fcd135.png) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 14, 2023, 11:54am UTC](https://discuss.elastic.co/t/working-days-how-to-find/332404/10 "2023-06-14T11:54:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.