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.
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!
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.
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.
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!