Optimizing search to display week-over-week percentage change of event count

[wu_weidong]

Hi all,

I'm trying to display a week-over-week percentage change of event count collected for various countries, and display that percentage on the choropleth map.

• The time window is a rolling window of the past 7 days (including today) compared with the 7 days before that, so it's not always fixed to start on a Sunday.
• I also have to dedup `fieldA` and `fieldB` for each of the 7 days, i.e. if fieldA=5, fieldB=6 appears twice in each of the two 7-day period in the same country, I would do a `dedup` and the event would be counted once for both 7-day periods

For example:

Country	LastWeek	ThisWeek	PercentChange
US	10	10	0
Japan	5	4	-20
Australia	4	5	25
China	2	0	-100
Canada	0	2	400

For the Canada case, I'm dividing it by 1 instead of 0 to calculate PercentChange, because I still want to show that there is a change in the number of events.

I would then display the Percentage change values on the map over the respective countries.

The query I have is 

index=test earliest=-6d@d latest=now()
| bin _time span=7d
| dedup _time fieldA fieldB country
| stats count as ThisWeek by country
| append [search index=test earliest=-13d@d latest=-6d@d
   | bin _time span=7d
   | dedup _time fieldA fieldB country
   | stats count as LastWeek by country ]
| stats values(*) as * by country
| eval LastWeek=if(isnotnull(LastWeek), LastWeek, 0)
| eval ThisWeek=if(isnotnull(ThisWeek), ThisWeek, 0)
| eval divideBy=if(LastWeek=0, 1, LastWeek)
| eval PercentChange=round((ThisWeek - LastWeek)/divideBy * 100, 2)
| stats values(PercentChange) as Percentage_Change by country
| geom geo_countries featureIdField=country

I'm getting the map to display the values, however, my query takes a long time to parse and run. Can I optimize it to make it run faster?

Thank you!

[rnowitzki · Accepted solution]

Hi @wu_weidong ,

I came up with something that seems to work. 

| makeresults
| eval ID = "1 2 3 4 5 6 7 8 9 10" 
| makemv ID
| mvexpand ID
| eval country = case(ID=1,"US",ID=2,"Australia",ID=3,"Japan",ID=4,"China",ID=5,"Canada",ID=6,"US",ID=7,"Australia",ID=8,"Japan",ID=9,"China",ID=10,"Canada")
| eval count = case(ID=1,"10",ID=2,"4",ID=3,"5",ID=4,"0",ID=5,"4",ID=6,"10",ID=7,"5",ID=8,"4",ID=9,"2",ID=10,"0")
| eval _time = case(ID=1,_time,ID=2,_time,ID=3,_time,ID=4,_time,ID=5,_time,ID=6,_time-700000,ID=7,_time-700000,ID=8,_time-700000,ID=9,_time-700000,ID=10,_time-700000)

| timechart span=7d sum(count) as EventCount by country
| eval Period=if(_time < relative_time(now(), "-7d@d"), "LastWeek", "ThisWeek")
| transpose header_field=Period
| eval PercentChange= if(LastWeek!=0,(ThisWeek-LastWeek)/LastWeek*100,ThisWeek*100)
| where column!="_time" AND column!="_span" AND column!="_spandays"
| rename column as country
| fields - LastWeek, ThisWeek
| geom geo_countries featureIdField=country

The first 7 rows are just to create some sample data that I could work with.
You need the stuff beginning with the timechart command.

I used transpose instead of streamstats. so that you can work with the LastWeek and ThisWeek columns to calculate the PercentChange.

You will have to play around with the coloring of the map. 
Maybe make the ones where the LastWeek is 0 default to 100, so you have an expected range (-100 to +100) that you can work with. 

Hope it helps.
BR
Ralph

--
Karma and/or Solution tagging appreciated.

[wu_weidong]

Awesome!  I had to change 

| timechart span=7d sum(count) as EventCount by country

to 

| timechart span=7d count as EventCount by country

 Otherwise, it worked great! Thanks!