Charting timeframes from a lookup table.

[TooManyQuestion]

Hello! I'm trying to do a charting task from a lookup table and can't seem to nail down a solution.

This is the essentially format of my data in the csv.

Status	Opened Date	Closed Date
Open	4/3/2020	TBD
Closed	4/3/2020	9/10/2020
Open	4/3/2020	TBD
Open	4/3/2020	TBD
Closed	5/6/2020	7/4/2020
Open	8/6/2020	TBD

Essentially what I would like to do is create a line chart that illustrates the events status over its time frame. Below would be an example of the above data.

Any help to this would be extremely appreciated!

[ITWhisperer]

Hi @TooManyQuestion 

I answered a similar problem here 

Essentially, in your case, I would create an event for every day that the item is open. You would have to choose an end date to replace TBD, perhaps the latest date in your data, or todays date. You can then count the number of events for each day, and plot your chart accordingly.

As I said in my solution, it is complicated, and possibly not efficient, but it would provide the chart you are looking for.

[ITWhisperer · Accepted solution]

Hi @TooManyQuestion 

This is a much simpler way given that you are using a line chart and don't need events for every day, just days when things change

| makeresults
| eval events="Open	4/3/2020	TBD
Closed	4/3/2020	9/10/2020
Open	4/3/2020	TBD
Open	4/3/2020	TBD
Closed	5/6/2020	7/4/2020
Closed	5/6/2020	8/6/2020
Open	8/6/2020	TBD"
| rex max_match=0 field=events "(?<events>.+)\n*"
| mvexpand events
| rex field=events "(?<Status>\w+)\s+(?<Opened>\d+/\d+/\d+)\s+(?<Closed>.+)"
| fields Status Opened Closed
| fields - _time

| eval _time=strptime(Opened,"%m/%d/%Y")
| eval closedate=strptime(Closed,"%m/%d/%Y")
| fieldformat closedate=strftime(closedate,"%Y-%m-%d")
| eval Status=if(Status="Closed",mvappend(Status,"Open"),Status)
| mvexpand Status
| eval _time=if(Status="Closed",closedate,_time)
| stats count as tickets by _time Status
| eval tickets=if(Status="Open",tickets,-tickets)
| stats sum(tickets) as change by _time
| streamstats sum(change) as tickets
| fields - change

[Nisha18789]

Hi @TooManyQuestion , by "line chart that illustrates the events status over its time frame" do you mean count of events status over time? What does the number represent on the Y-axis?

If above is true, try this, it will need additional logic for sequential date representation on the x-axis, but let me know first what is expected here.

|inputlookup <yourlookupname>

|stats count(Status) as count by Opened Date