Comparing Dates in Results- How do I get a laterdate?

[jedimuffin]

Hello, I have the search built that generates the results I want. But, the goal is to also be able to track high number of online orders after someone made a retail order.

index=data sector=Retail
| stats earliest(_time) as firstretailapp latest(_time) as lastretailapp by username
| join username
               [| search index=data sector=Online
                 | stats earliest(_time) as firstonlinesale latest(_time) as lastonlinesale by username]
| convert ctime(firstretailsale) ctime(lastretailsale) ctime(firstonlinesale) ctime(lastonlinesale)

When these results populate, I can not get the firstonlinesale to be a later date than the lastretailsale. I have tried | eval difference =time1-time2, and where difference >1 and other command searches to try and match up something but am unsuccessful. 

Thanks!

[jedimuffin]

| stats earliest(*_*) as *_* by username

this  drops it to one column of just a list of just usernames 

[yuanliu]

I think trying to use double wildcard (earliest(*_*) as *_*) was a mistake.

index=data sector IN (Retail, Online)
| stats earliest(_time) as first_order latest(_time) as last_order by username sector
| eval first_retail = if(sector == "Retail", first_order, null())
| eval first_online = if(sector == "Online", first_order, null())
| eval last_retail = if(sector == "Retail", last_order, null())
| eval last_online = if(sector == "Online", last_order, null())
| stats earliest(first_retail) as first_retail earliest(first_online) as first_online  latest(last_retail) as last_retail latest(last_online) as last_online by username
| where first_online > last_retail
| convert ctime(first_retail) ctime(last_retail) ctime(first_online) ctime(last_online)

To clarify: the foreach subsearch is an iteration over first_order, last_order in order to populate first_retail, first_online, last_retail, and last_online as expanded in the above.  But the double wildcard probably wouldn't work. 

[jedimuffin]

thank you! this works except for the conditional comparison of the where clause. but, I can just export the results and go from there. Thanks!

[yuanliu]

Avoid expensive join.  Try

index=data sector IN (Retail, Online)
| stats earliest(_time) as first_order latest(_time) as last_order by username sector
| foreach *_order
    [eval <<MATCHSTR>>_retail = if(sector == "Retail", <<FIELD>>, null())
    | eval <<MATCHSTR>>_online = if(sector == "Online", <<FIELD>>, null())]
| stats earliest(*_*) as *_* by username
| where first_online > last_retail
| convert ctime(first_retail) ctime(last_retail) ctime(first_online) ctime(last_online)

Hope this helps

[jedimuffin]

thank you. But I get 0 results and I dont understand what the search is doing. Also, in the where clause, that is the first time first_online and last_retail are mentioned? I will keep working on it