Thanks @ololdach for the feedback on using multivalue field. Yes, only the SiteID field is in the indexed events.
After I try this, it seems the search is not recognizing 'earliest_date' and 'latest_date' as time fields. It does not calculate the KPI for each row between these time windows. Instead uses the default time range picker. I believe that that is why I did this renaming in my earlier sample code:
| rename earliest_date as earliest, latest_date as latest
Also, I need to apply this to a lookup csv with same format, but where EVENT_ID can have multiple SiteID fields and SiteID can have multiple EVENT_IDs. (added fourth row to illustrate this):
YEAR, SiteID, earliest_date, latest_date, EVENT_ID
2019, AB111, 1560988800, 1562112000, ABSE00350
2019, AB111, 1562198400, 1563321600, ABSE00351
2019, AB111, 1548892800, 1550016000, ABSE00352
2019, AB112, 1548892800, 1550016000, ABSE00352
Ideal query Output: (KPI computed for unique combination of SiteID & EVENT_ID)
SiteID, KPI, EVENT_ID
AB111, 68.4, ABSE00350
AB111, 74.3, ABSE00351
AB111, 22.1, ABSE00352
AB112, 34.5, ABSE00352
... View more