hello
from the code below, i would like to be able to add a new colum in my table panel which calculate the percentage trend between the previous count and the next count for each line
Example
Month count Trend
January 200 --
February 300 +34%
March 100 -66%
index="toto" sourcetype="tutu" assigned_group="titi"
| dedup incident_number
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort -Month
thanks for your help
Are your example numbers what you want?
Between Jan and Feb
by between Feb and Mar
so in your example are you looking to show,
a) when negative, what % the smaller value is of the larger value
b) when positive, what % the difference between the values is of the larger value
or something else.
Note that the sort -Month will sort in reverse chronological order, so with the %Y-%m your order will be March,Feb,Jan. Naturally if you then use delta, that will give different results.
So you could use
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(diff/count*100,0)
Note that if you change the sort order the delta values are different. In the ascending sort, then change from 300 to 100 shows -200%.
So it sort of depends if Feb in your example is needed to show
a) Feb is a 50% growth over Jan
b) Jan was 33% lower than Feb
but to give you your example, you would have to do
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)
NB: Ascending sort
Run anywhere example
| makeresults
| rename COMMENT as "Setting up data to show example"
| eval f="2019-01,200#2019-02,300#2019-03,100"
| makemv delim="#" f
| mvexpand f
| rex field=f "(?<Month>[^,]*),(?<count>.*)"
| fields - f, _time
| sort Month
| eval diff=0
| delta count as diff
| eval percentage=round(if(diff>0,diff/count*100,diff/(count-diff)*100),0)
hi perfect demonstration but for the percentage calculation i have the same question asked to
jitendragupta
I guess you need to express what the percentage is that you wish to see
try this:
index="toto" sourcetype="tutu" assigned_group="titi"
| dedup incident_number
| eval Month=strftime(_time,"%Y-%m")
| stats count by Month
| sort -Month
| delta count as Diff
| eval percentageTrend =(Diff/count)*100
thanks it works but the calculation of the percentageTrend is strange
to my mind its not Diff/ count but (count-1/count)*100?
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-1y@month"), _time)
| makecontinuous span=1d
| eval incident_number="incedent_number_".(random() % 25 + 1)
| bin span=1month _time
| stats dc(incident_number) as count by _time
| eval Month=strftime(_time,"%B, %y")
| delta count as diff
| eval Trend=(diff / count * 100)."%"
| table Month count Trend
Is Month
in this format?
Thanks it seems to be good
Month format is 😐 eval Month=strftime(_time,"%Y-%m")
last question : is it possible to have + between the Trend when the Trend is positive??
| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-2y@month"), _time)
| makecontinuous span=1d
| eval incident_number="incedent_number_".(random() % 25 + 1)
| bin span=1month _time
| stats dc(incident_number) as count by _time
| eval Month=strftime(_time,"%Y-%m")
| delta count as diff
| eval Trend=(diff / count * 100)
| autoregress Trend as Trend_diff
| eval positive_diff=if(Trend >= 0 , Trend - Trend_diff,NULL)
| eval Trend=Trend."%", Trend_diff=positive_diff."%"
| table Month count Trend Trend_diff
Is this it?
yes thanks
please accept the answer