Timechart fillnull with append search

[ecedwards]

So, I'm trying to come up with a way to compare data from this year and last year into a Single Value Graph but I am unable to force the 0 value into the first timechart with it's own date. This is my search query.

index=* host=*obe2e*ap* code=NAV7000 
| timechart span=1d dc(confirmationNumber) as "Stats"
| append [search index=* source=*funnel*'step5'  earliest=-1y+1d@h latest=-1y+1d+1h@h | timechart span=1d count as "Stats"] 
| sort _time

I've tried adding | table _time, Stats | fillnull Stats but due to it already having a value cause by the append search, it won't work. I also tried renaming both fields, the first one into Stats2 and second one into Stats and apply the same concept | table _time, Stats, Stats2 | fillnull Stats2 and it works but it registers under the same date, and not a different one, therefore the Single Value visualization doesn't work.

Help! 🙂

[woodcock]

Your logic (using dc one place and count the other) seems like it is highly likely to be INCORRECT, but, presuming that it is not, try this:

(index=* host=*obe2e*ap* code=NAV7000) OR (index=* source=*funnel*'step5'  earliest=-1y+1d@h latest=-1y+1d+1h@h)
| timechart span=1d dc(confirmationNumber) AS ThisYear count(eval(source="*funnel*'step5')) AS LastYear
| stats max(*) AS *
| eval diff = ThisYear - LastYear