Counting the implementation days of an event based on Start and End date

[Paul_Dixon]

Hi all, hoping someone can help me with this query.

i have a data set that looks at a process and how long it takes to implement. for example, each event will be populated with a start date and an end date. i want to create a calendar view that shows the schedule of the processes in implementation, for example:

process 1 start date 12/08/2024, end date 16/08/2024 (5 days implementation)
process 2 start date 12/08/2024, end date 12/08/2024 (1 day implementation)
process 3 start date 13/08/2024, end date 15/08/2024 (3 days implementation)
process 4 start date 14/08/2024, end date 16/08/2024 (2 days implementation)

I want to be able to produce a graph or a calendar view that will show how many process' we have in implementation, counting each day of their implementation period (based on start and end date)

so for the above example it would look like:

Date                        count of Process' in implementation
12/08/2024       2 (process 1 and 2)

13/08/2024       2 (process 1 and 3)

14/08/2024       3 (process 1, 3 and 4)

15/08/2024       3 (process 1, 3 and 4)

16/08/2024       2 ((process 1 and 4)

any help greatly appreciated 

[ITWhisperer · Accepted solution]

You could do something like this

| rex "process (?<process>\d+) start date (?<start>\S+), end date (?<end>\S+)"
| eval startdate=strptime(start,"%d/%m/%Y")
| eval enddate=relative_time(strptime(end,"%d/%m/%Y"), "+1d")
| eval days=mvappend(startdate, enddate)
| eval row=mvrange(0,2) 
| mvexpand row
| eval _time=mvindex(days, row)
| eval count=1-(row*2)
| stats sum(count) as change by _time
| streamstats sum(change) as total
| makecontinuous _time
| filldown total
| fillnull value=0 change

[Paul_Dixon]

Thanks for this. i was able to utilise your solution to build a working process for what i need!