Hello,
I want to compare the value of the week before last with the value of my search (last week) and put the percentage growth/decrease in the result.
Currently I can display the value of the previous week to me and in another search the value of the week before last. However, I cannot compare the two values.
My query looks like this:
In search I set last week
"..." | stats count as lastweek | append [search"..." earliest=-14d latest=-7d | stats count as lastweek1
Result:
Lastweek lastweek1
XX
XX
Desired Result:
Lastweek lastweek1 Change
XX XX 50%
This gives me two values, but I can't compare them, at least I don't know how. Of course it would be even more elegant if I had the value already in the first query without having to use append. Is there a solution, that I have the values of the previous week in the same query, so that I can compare both values?
Hi @exchanger,
try something like this:
Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats
count(eval(week="This week")) AS "Count this week"
count(eval(week="Last week")) AS "Count last week"
avg(eval(if(week="This week",DURATION,"")) AS "AVG this week"
avg(eval(if(week="Last week",DURATION,"")) AS "AVG last week"
Ciao.
Giuseppe
Hi @exchanger,
you have to identify the week in your search using the eval command:
Your_search earliest=-14d latest=now
| eval week=if(_time-now()<604800,"This week","Last week")
| stats count By week
Ciao.
Giuseppe
Hello,
So I get then only one result (see screenshot)
The goal would be
Last week Week before Last week Change
50 100 xx%
Hi, there I can set what I want. But maybe I understand your question or answer wrong.
Hi @exchanger,
Sorry: my error!
Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats count By week
Ciao.
Giuseppe
Thank you, so I get both values. However, if I want to put them in a ratio, I don't know how that should work. Because I don't have two variables but the variable "week" which has two values.
Or is there a way with your command to calculate the increase/decrease from the second to the last week?
Hi @exchanger,
use eval inside stats command:
Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats count(eval(week="This week")) AS "This week" count(eval(week="Last week")) AS "Last week"
so you have two values on the same row and you can calculate a percentage.
Ciao.
Giuseppe
Thank you very much. Now I can also put these in relation. However, I still have one last question. Apart from stats count, I am also asking avg, max etc. So the command looks like this :
stats count(eval(week="This week")) AS "This week" count(eval(week="Last week")) AS "Last week", avg(DURATION) as AVG, max(DURATION) as MAX
I want to have the comparison from the last week to the week before last not only for stats count but also for AVG max etc. I am not sure how to change the command avg(DURATION) to give me the data from last week and how to change it to give me the data from the week before last. Since I have already defined "Last week" and "this week" I would probably have to put them in the brackets "(DURATION"), I just wonder how?
Hi @exchanger,
try something like this:
Your_search earliest=-14d latest=now
| eval week=if(now()-_time<604800,"This week","Last week")
| stats
count(eval(week="This week")) AS "Count this week"
count(eval(week="Last week")) AS "Count last week"
avg(eval(if(week="This week",DURATION,"")) AS "AVG this week"
avg(eval(if(week="Last week",DURATION,"")) AS "AVG last week"
Ciao.
Giuseppe