How to count and sum fourth column if second and third column are certain value and group by first column?

[Username1]

So my data structure has four columns: "Month", "Status", "Accepted", "Value". As the title suggest I'm trying to determine two things: 1) the total values per month, 2) the total values per month where "Status=Done and Accepted = Done". I have the query working for the first one but I'm having difficulty with the second. Does anyone have any ideas how to do this? Thanks!

index=index
|table Month, Status, Accepted, Value
| eval _time=strptime(scan_date,"%Y-%m-%d")
| stats sum(Value) as total_value_per_month by Month

[kmorris_splunk]

index=YOURINDEX sourcetype=YOURSOURCETYPE
| stats sum(Value) as TotalValue by Month 
| append 
[ searchindex=YOURINDEX sourcetype=YOURSOURCETYPE Status="Done" Accepted="Done" 
| stats sum(Value) as TotalVal_Done by Month] 
| stats values(TotalValue) as TotalValue values(TotalVal_Done) as TotalVal_Done by Month

[thambisetty]

index=index Status=Done Accepted=Done

| stats sum(Value) by Month

[Username1]

Hi @thambisetty  Thanks for responding! If i do that how will I count where Status = 'Not Done" by Month?

[thambisetty]

I believe you are looking for different query?

If yes,

index=index Status=“not done” 

| stats sum(Value) by Month.

up vote if it solves your problem.

[Username1]

@thambisetty Is there no way I can have one query? The end goal is to have a visual

[thambisetty]

Write down all your cases to combine. We need to use Case statement.

for example: 

if Status=Done Accepted=Done -> Done

if status =“Not Done” -> “Not Done”

and expected output in table format.

[Username1]

@thambisetty   So my data structure has four columns: "Month", "Status", "Accepted", "Value". The two things I'm trying to graph are: 1) the total values per month no matter what Status/Accepted, 2) the total values per month where "Status=Done and Accepted = Done".  

[thambisetty]

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,”1”,”0”)

| stats sum(Value) as all , sum(done) as done by Month

[Username1]

@thambisetty  I tried running that and it returned "Error in 'eval' command: The expression is malformed. An unexpected character is reached at '”1”,”0”)'."

If I do that how would the `done`  eval be able to sum all of the Values, as Values range from .25 - 10

[thambisetty]

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,”1”,”0”)

| stats sum(Value) as all , sum(done) as done by Month

replace double quotes with double quotes from your key board.

your query is not clear.

[thambisetty · Accepted solution]

Check this and upvote if it solves your problem.