Compare one field against itself (Chart)

[JoshuaJohn]

I have a chart that gives me serial numbers, some of the spots for serial numbers are empty. I want to compare how many are empty vs the total number of serials I have (That are not empty).

So basically a bar graph with 1 bar being total number of serials
The other bar being number of empty serials

|inputlookup Serial_Report.csv |stats count by serial_number|stats sum(serial_number) AS totalSerial | fillnull value="XXX" serial_number | search serial_number="XXX" | stats count by serial_number|stats sum(serial_number) AS totalEmptySerial |fields totalEmptySerial, totalSerial

Any Ideas?

[somesoni2 · Accepted solution]

Give this a try

|inputlookup Serial_Report.csv |stats count by serial_number | eval is_empty=if(serial_number="" OR len(trim(serial_number))=0,1,0)
| stats sum(is_empty) as totalEmptySerial count as totalSerial

[JoshuaJohn]

https://pasteboard.co/8fKoBncto.png
Not exactly, it doesn't appear to recognize the empty fields

[somesoni2]

How about this

|inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval is_empty=if(serial_number="XXX",1,0)
 | stats sum(is_empty) as totalEmptySerial count as totalSerial

[JoshuaJohn]

Now I am getting 1 count result for empty, should be around 300.

[somesoni2]

Pheww. Try this

 |inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval is_empty=if(serial_number="XXX",count,0)
  | stats sum(is_empty) as totalEmptySerial sum(count) as totalSerial

[JoshuaJohn]

Ah so close, I still need two bars though. I am not sure if it is possible

Basically one would say 300, the other would be 20,000

This is what your search has created so far
https://pasteboard.co/8hgrpV061.png

[somesoni2]

Let this be the final attempt. Try this

|inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval Metrics=if(serial_number="XXX",split("totalEmptySerial,totalSerial",","),"totalSerial")
   | stats sum(count) as count by Metrics

[JoshuaJohn]

Thank you!! Perfect.