How to include a duration that started out of time range of the search into stats/timechart command?

[akocak]

I have a dataset like below:
Ticket#| StartDate | EndDate

In my search, I am more into EndDate of the tickets as

|eval _time=EndDate | eval Duration = EndDate - StartDate

Later on if I try to search something like

|timechart avg(Duration) by TicketNum

with YTD time range, it excludes the data that started in 2016 and ended in 2017. It only shows correct numbers, if I set earliest to min time of the ticket from last year. This time however, I don't get my time chart for YTD.

What should be my approach to get desired result-set? Do you guys have any similar experience. Thanks up front for your time.

[somesoni2 · Accepted solution]

Since the time range picker values applies on the _time value available in base search (before first pipe, where I believe _time is based on StartDate value), it's missing events which have StartDate in 2016. For that you should set your time range to include 2016 and add a where clause in your search to filter YTD data based on updated _time/EndDate, like this

your base search with timerange earliest=-1y@y latest=now 
|eval _time=EndDate | where _time>=relative_time(now(),"@y")
| eval Duration = EndDate - StartDate
|timechart avg(Duration) by TicketNum

[akocak]

you hit the bull eye again somesoni , please check my other questions too 🙂