topic Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4")) in Splunk Search

=SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

kvr — Thu, 14 Feb 2019 07:00:09 GMT

Could you please help me to convert above excel formula into query ?? Thanks in advance.
Need to filter one date and status then count by the Week.

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

woodcock — Thu, 14 Feb 2019 07:16:40 GMT

show us your raw events and the desired final output. Throw the Excel formula in the dumpster.

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

kvr — Thu, 14 Feb 2019 08:52:23 GMT

We have to get the status count by week.
Week should be based on Created field.
Week needs to be created using Created field.

Example if Tickets are created on 12,13 or 14th Feb then created week will be 14th Feb.
So now we have to get the status(Status1,Status2,Status3..etc) count by Week.

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

DMohn — Thu, 14 Feb 2019 10:32:08 GMT

I am making the following assumtions:
- you have a field "created" which contains a date in the format "YYYY-MM-DD"
- you have a field called "status" which has the status values

If this applies you can search like this:

 index=$yourindex sourcetype=$yoursourcetype | eval week=strftime(strptime(created,"%Y-%m-%d"),"%W") | chart count by week, status

If your creation date has another format, you need to adjust the strptime parameters accordingly. If you need further assistance, feel free to comment.

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

kvr — Thu, 14 Feb 2019 11:24:53 GMT

Hi @DMohn

Thanks

My requirement is

I have 2 fields,
1.Status(Values are Status1,Status2..etc)
2.Created(MM/DD/YYYY)

So here I have to create Week Field(Should use Created Field).If created date is (9,10,11,12,13,14 or 15 then Week will be 15th of Feb i.e Friday).

Statistics should display like below

Week Status1 Status2 Status3 Status4
28-Sep 66 10 51 5
5-Oct 67 10 52 5
12-Oct 67 10 52 5
19-Oct 69 10 54 5
26-Oct 69 10 54 5
2-Nov 69 10 54 5

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

DMohn — Thu, 14 Feb 2019 11:28:27 GMT

Okay, just for clarification - the "Week" field must reflect the Friday of the current week? Or could it be the calendar week number?

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

kvr — Fri, 15 Feb 2019 02:51:34 GMT

Friday of the week

Saturday to Friday is the week. Which ever created these days then that ticket will come under that week.

Re: =SUM(COUNTIFS(F38:F800,"<"&[@Week],$D38:$D800,"<>Status1",D38:D800,"<>Status2",D38:D800,"<>Status3",D38:D800,"<>Status4"))

woodcock — Mon, 18 Feb 2019 20:22:07 GMT

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo
| eval _time = relative_time(strptime(Created, "%m/%d/%Y"), "@w5")
| chart count BY _time Status

It becomes very easy to help when you specify your needs the way that we ask you to. The problem was fairly trivial once you got around to doing that.