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
... View more