I have a bar chart with 4 values. I would like to stack them by 1 field.
The code I am using:
index=snow assignment_group_name=*israel* (dv_state="Ope*" OR dv_state="Clos*") (priority=2 OR priority=4) | dedup number | eval newTime = strftime(_time,"%Y-%m-%d") | eval year = substr(substr(newTime,3,4),1,2) | eval month = substr(substr(newTime,6,7),1,2) | eval realDate = year+"."+month | eval monthCreated = substr(substr(sys_created_on,6,7),1,2) | eval yearCreated = substr(substr(sys_created_on,3,4),1,2) | eval dateCreated = yearCreated+"."+monthCreated | where tonumber(substr(dateCreated,1,2)) > 16 | replace "Closed Complete" with Closed "Closed Incomplete" with Closed "Closed Skipped" with Closed "Pending Customer" with Pending in dv_state | replace 4 with Normal 2 with High in priority | table number realDate dateCreated dv_state priority _time | chart count(eval(realDate==dateCreated)) as Opened count(eval(dv_state=="Closed")) as Closed over realDate by priority
Photo of the result with the current code:
If I choose the stacked option it stacks all 4 options (in legend). I want to stack them only by priority field so that the result will be similar to this:
Is it possible?
you cannot do multi-layer axis like you can in excel. the closest you could get is to create a field like:
|eval Status=if(count(eval(realDate==dateCreated)),"Opened","Closed")|eval YearMonthStatus=year+"."+month+"."+Status| chart limit=0 count by priority YearMonthStatus
splunk pivot is not like excel pivot. splunk pivot uses data models to create tables and charts. you can accelerate the data model to allow the pivot table to run faster.
Looks that you should stats by united field realDate_status try to change
| chart count(eval(realDate==dateCreated)) as Opened count(eval(dv_state=="Closed")) as Closed over realDate by priority
to something like
| eval status=case(realDate==dateCreated, "opened", dv_state=="Closed", "closed")
| eval realDate_status=realDate."_".status
| eval priority_high=if(priority=="High","1",NULL)
| eval priority_normal=if(priority=="Normal","1",NULL)
| stats sum(priority_high) as High sum(priority_normal) as Normal by realDate_status
| sort - limit=10
which will give you result similar to your expectations