How can I count by date, field, and range?

[tkwaller_2]

Hello

I have some steps in a table that have a due date and SLA tied to them. Im trying to sum number of SLA days by date range.

Heres an example table:

Name      SLA        Due Date
Sample 1    5  2018-05-03 22:59:17.246000
Sample 2    10   2018-04-27 22:59:17.246000
Sample 3    5  2018-03-20 22:59:17.246000
Sample 4    5  2018-03-13 22:59:17.246000
Sample 5    2  2018-02-27 22:59:17.246000

I'm trying to total SLA by Date Range -
"Sum of SLA days for Next 30 days"

"Sum of SLA days for 31-60 days"

"Sum of SLA days for 61-90 days"

"Sum of SLA days for 90+ days"

[somesoni2 · Accepted solution]

Give this a try

your current search giving table with fields Name SLA "Due Date"
| eval DateRange=round((strptime('Due Date',"%Y-%m-%d %H:%M:%S.%6N")-now())/86400)
| eval DateRange=case(DateRange<=30,"Next 30 days",DateRange<=60,"31-60 days", DateRange<=90,"61-90 days",true(),"90+ days)
| stats sum(SLA) as "total SLA" by DateRange
| eval DateRange="Sum of SLA days for ".DateRange