index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv")
|where AMOUNT!="NA"
|eval latestdatestart=relative_time(now(),"-10d@d"),latestdateend=now(),parsedate=strptime(TXNDATE,"%m/%d/%Y")
|where parsedate >= latestdatestart AND parsedate < latestdateend
|stats sum(AMOUNT) as latestdatevalue by UiCountryCode parsedate|sort parsedate
|eval latestdate=strftime(parsedate,"%m/%d/%Y"), latestdatevalue=round(latestdatevalue,2)
| table UiCountryCode latestdate latestdatevalue lastday |eval latestdateformat=strptime(latestdate,"%m/%d/%Y"),lastdayformat = relative_time(latestdateformat,"-1d@d"),lastday=strftime(lastdayformat,"%m/%d/%Y")
|join UiCountryCode lastday [search index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv")
|where AMOUNT!="NA"
|eval lastdaystart=relative_time(now(),"-11d@d"),lastdayend=relative_time(now(),"-1d@d"),parsedate=strptime(TXNDATE,"%m/%d/%Y")
|where parsedate >= lastdaystart AND parsedate < lastdayend |stats sum(AMOUNT) as lastdayvalue by UiCountryCode parsedate
|sort parsedate
|eval lastday=strftime(parsedate,"%m/%d/%Y"),lastdayvalue=round(lastdayvalue,2)
| table UiCountryCode lastday lastdayvalue
|eval lastdayformat=strptime(lastday,"%m/%d/%Y"),lastweekformat =
relative_time(lastdayformat,"-7d@d"),lastweek=strftime(lastweekformat,"%m/%d/%Y")]
|join UiCountryCode lastweek[search index="ocdm" source IN ("covid_collection.csv","covid_collection_lcpr.csv","covid_collection_cl.csv", "covid_collection_cr.csv")
|where AMOUNT!="NA"
|eval lastweekstart=relative_time(now(),"-17d@d"),lastweekend=relative_time(now(),"-6d@d"),parsedate=strptime(TXNDATE,"%m/%d/%Y")
|where parsedate >= lastweekstart AND parsedate < lastweekend
|stats sum(AMOUNT) as lastweekvalue by UiCountryCode parsedate
|sort parsedate
|eval lastweek=strftime(parsedate,"%m/%d/%Y"),lastweekvalue=round(lastweekvalue,2)
| table UiCountryCode lastweek lastweekvalue]
|eval kpi="COLLECTION AMOUNT",_time=relative_time(now(),"-0d")
|fields - latestdateformat,- lastweekformat,- lastdayformat
why don't you stop use join
and make flag like:
| eval flag=case(parsedate > relative_time(now(),"-10d@d") AND parsedate < now() ,"lastdate", ....)
....
| stats sum(AMOUNT) as AMOUNT by UiCountryCode parsedate flag
...
I've reformatted the query to make it easier to read.
Please explain what the query is attempting to do. What are the desired results? What makes you think it needs to be optimized?