Hi, have you tried to do something like this ? I need to calculate the duration and then calculate a % availability line chart(timechart). I could do it with 2 months consecutive but it is not a good solution. Input: begin end 2020-03-06 2020-07-06 Expected output: begin end 2020-03-06 2020-03-31 2020-04-01 2020-04-30 2020-05-01 2020-05-31 2020-06-01 2020-06-30 2020-07-01 2020-07-06 Thanks in advance. | makeresults
| eval begin = "2020-01-15" , end ="2020-01-30", id="111"
| makemv delim="," id
| makemv delim="," begin
| makemv delim="," end
| append
[| makeresults
| eval begin = "2020-02-15" , end ="2020-02-28", id="111"
| makemv delim="," id
| makemv delim="," begin
| makemv delim="," end]
| append
[| makeresults
| eval begin = "2020-03-06" , end ="2020-07-06", id="111"
| makemv delim="," id
| makemv delim="," begin
| makemv delim="," end]
| append
[| makeresults
| eval begin = "2020-10-15" , end ="2020-10-30", id="111"
| makemv delim="," id
| makemv delim="," begin
| makemv delim="," end]
| append
[| makeresults
| eval begin = "2020-12-15" , end ="2020-12-30", id="111"
| makemv delim="," id
| makemv delim="," begin
| makemv delim="," end]
| eval begin_m = substr(begin,6,2), end_m = substr(end,6,2)
| eval x= if('begin_m' != 'end_m' ,1,0)
| where x ==1
| fields- begin_m end_m x
| eval end_unix=strptime(end,"%Y-%m-%d")
| eval begin_unix=strptime(begin,"%Y-%m-%d")
| eval begin_new=strftime(relative_time(begin_unix,"+1mon@mon"), "%Y-%m-%d %H:%M:%S+%2Q")
| eval end_new=strftime(relative_time(begin_unix,"+1mon@mon-1second@second"), "%Y-%m-%d %H:%M:%S+%2Q")
| eval end = mvappend(end,end_new), begin = mvappend(begin,begin_new)
| mvexpand end
| mvexpand begin
| eval begin_m = substr(begin,6,2), end_m = substr(end,6,2)
| eval x= if('begin_m' != 'end_m' ,1,0)
| where x !=1
| fields - begin_m begin_new begin_unix end_m end_new end_unix
| eval end_unix=strptime(end,"%Y-%m-%d")
| eval begin_unix=strptime(begin,"%Y-%m-%d")
| eval duration_d = round((end_unix-begin_unix)/(3600*24),2)
| fields - end_unix begin_unix
... View more