Pie charting data below

[ranjitbrhm1]

Hello Guys, I have a query. I want to show the following data as pie where the pie visualisation should have a count of -ve value, +ve value and values showing pending with the projects to which they are attached to. Below is the data tabled.

    30  Trendmicro implementation
    30  Trendmicro implementation
    Pending Trendmicro implementation
    Pending Trendmicro implementation
    Pending Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation


426 Mcafee installation
426 Mcafee installation
426 Mcafee installation
Pending Mcafee installation
-37 Mcafee installation
-37 Mcafee installation
-37 Mcafee installation
-37 Mcafee installation

So ideally for me the pie chart would be having 3 slices where +ve values are 1 piece, --ve value is 1 piece and pending is also 1 pie. Is there a method where we can visualise the data?

[ranjitbrhm1]

This is proper. Thanks @niketnilay for taking effort in writing the query for me.
Thanks

[niketn · Accepted solution]

@ranjitbrhm1, following is a run anywhere example with mocked up data based on your sample data and query provided.
I have added a transpose command at the end for creating the pie chart. Mock data has 2 On-Time, 1 Delayed and 1 Pending task for the Project.
PS: Command from | makeresults till | fields - data cook up dummy data, you would need to use your own query instead.

|  makeresults
|  fields - _time 
|  eval data="Splunk Implementation,Imran,2/2/2018,,3/2/2018,Install os,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign host name,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign IP address,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,4/12/2018,Install splunk,2/2/2018,3/5/2018"
|  makemv data delim=";"
|  mvexpand data
|  eval data=split(data,",")
|  eval Project_Title=mvindex(data,0), Project_Manager=mvindex(data,1), task_start_date=mvindex(data,2), task_end_date=mvindex(data,3), task_deadline=mvindex(data,4), Tasks=mvindex(data,5), project_start_date=mvindex(data,6), project_end_date=mvindex(data,7), project_completed_date=mvindex(data,8)
|  fields - data
|  eval deadline_epoch=strptime(task_deadline,"%m/%d/%Y"), end_epoch=strptime(task_end_date,"%m/%d/%Y")
|  eval p_status=round(((end_epoch-deadline_epoch)/86400),0)
|  stats count(eval(isnull(p_status))) as "Pending" count(eval(isnotnull(p_status) AND p_status>=0)) as "On-time"  count(eval(isnotnull(p_status) AND p_status<0)) as "Delayed" by Project_Title
|  transpose header_field=Project_Title column_name=Project_Title

Please try out and confirm!

[ranjitbrhm1]

Thank you all for the suggestions. I wrote a query very similar to @p_gurav answer
index="bsg" | eval deadline_epoch=strptime(task_deadline,"%m/%d/%Y")| eval end_epoch=strptime(task_end_date,"%m/%d/%Y") |eval diffrence=(end_epoch-deadline_epoch) |eval p_status=round((diffrence)/86400,0) | fillnull value=pending| stats count(eval(p_status>0)) as on_time, count(eval(p_status<0)) as delayed, count(eval(p_status=pending)) as deffered by Project_Title

But my challenge now is on the pie its not showing the delayed and on time correctly and deffered split by project name. My sample data looks like this. I uploaded it off a CSV that we created,

Project_Title   Project_Manager task_start_date task_end_date   task_deadline   Tasks   project_start_date  project_end_date    project_completed_date
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Install os  2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Assign host name    2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Assign IP address   2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Install splunk  2/2/2018    3/5/2018    

[niketn]

@ranjitbrhm1, what does the positive and negative values indicate? Do you need those to be represented as well?

[p_gurav]

You first need to extract those values(e.g. -18, pending etc) in field called count, then try something like :

| eval type=case(count<1, "-ve", count >= 1, "+ve", count=="Pending", "Pending") | stats count by type