How to count dates of a field that are over now() and Values with no entry?

[UMDTERPS]

I have a CSV that looks like the following: 

Organization	System	Scan Due  Date
ABC	Jack	7-Feb-21
ABC	Jill	9-May-20
123	Bob	Unspecified
123	Alice	Unspecified
456	James	10-Jan-21

How do I do a count of the " Scan Due Date Field" that shows all of the events that are Overdue, Not Expired, and Unspecified? ( I want to eventually want to put the results of that search into a pie chart. 

Any help is appreciated! 

[to4kawa · Accepted solution]

sample:

| makeresults
| eval _raw="Organization,System,Scan_Due_Date
ABC,Jack,1418459200
ABC,Jill,1593547277
123,Bob,Unspecified
123,Alicez,Unspecified
456,James,1625083277"
| multikv forceheader=1
| table Organization,System,Scan_Due_Date
| rename COMMENT as "the logic"
| eval status=case(Scan_Due_Date >= now(),"Not Expired",Scan_Due_Date="Unspecified",Scan_Due_Date,true(),"Overdue")
| stats count by status

try Visualization >> Pie Chart

[UMDTERPS]

| inputlookup scan_due_date.csv
| eval date = strptime('Scan Date', "%d-%b-%Y")
| eval duedate = if(isnull(date) OR date="", "Unspecified", date)
| eval status=case(duedate >= now(),"Not Expired",duedate="Unspecified",duedate true(),"Overdue")
| fields date duedate status
| stats count by status

Okay, figured it out. I had to convert the date using strptime using a capital "%Y" (Capital "%Y" is needed to convert "2020", lowercase "%y" would only work with "20"). Because striptime fills in "Unspecified" with blanks/null values, I had to do an eval "if" statement to fill in the blanks strptime created with "Unspecified" to bring it back. 

[UMDTERPS]

I put a strptime in and got it partially working:

| inputlookup scan_due_date.csv
|  eval date = strptime('Scan Date', "%d-%b-%y")
| eval status=case(date >= now(),"Not Expired",date="Unspecified",date true(),"Overdue")
| stats count by status

However, it's not counting the "Unspecified" field values. 

Any ideas?

[richgalloway]

How do you define "Overdue" and "Not Expired"?

[UMDTERPS]

Overdue (Expired) means the "Scan Due  Date" is a past date.
Not-Expired means the "Scan Due Date" is a date sometime in the future.