Show 2 bars in every "by" value bar chart

[matansocher]

Hi,

I have a problem I cant find the solution to.
I want to display 2 bar from each "by" field.
for example:
my report shows 1 bar for each month:

I need my reports to look a bit different:

for each month, I need to devide the columns by another field(dv_state), that has 7 possible values and I want to use only 2 (Ope*, Clos*).

The code I am using:

index=snow assignment_group_name=*israel* (dv_state="Clos*" OR dv_state="Ope*") (priority=2 OR priority=4) 
| dedup number 
| eval month = substr(substr(sys_updated_on,6,7),1,2) 
| eval year = substr(substr(sys_updated_on,3,4),1,2) 
| eval timeField = month+"."+year 
| replace 2 with "High" 4 with "Normal" in priority
| chart count over "timeField" by priority 

By the way, if there is a better way to show the month/year, I would love to hear, because if the 12/16 month added, it is added to the right (the end), and it is not sorted by time.

Thank you!

[DalJeanis · Accepted solution]

Here's a workaround. It gets you what you are asking for, but takes up a little extra real estate in order to label the columns and space them out as desired.

| gentimes start="06/01/2016:00:00:00" end="01/27/2017:00:00:00" increment=30d
| eval dv_state ="Closed Open Closed Open" | makemv dv_state | mvexpand dv_state
| eval priority ="2 4 4 4 4 2 4" | makemv priority | mvexpand priority
| eval temp=mvrange(1,1+random()%10) | mvexpand temp
| eval _time=starttime+86400*temp
| eval sys_updated_on=strftime(_time,"%Y-%m-%d")
| streamstats count as number
| table sys_updated_on dv_state priority number
| rename COMMENT as "The above just generates some test data."


| rename COMMENT as "First we pull the information and add up the stats."
| dedup number 
| eval timeField = substr(sys_updated_on,3,5)
| stats count(eval(priority=2)) as CountHigh count(eval(priority=4)) as CountNormal by dv_state timeField
| eval Xval=timeField." ".dv_state

| rename COMMENT as "Now we add a spacer and a label."
| appendpipe [| where dv_state="Closed" | eval CountHigh=0, CountNormal=0,Xval=timeField."1", dv_state="blank"]
| appendpipe [| where dv_state="Closed" | eval CountHigh=0, CountNormal=0,Xval=timeField, dv_state="date"]

| rename COMMENT as "We use chart to format the data."
| chart sum(CountHigh) as High sum(CountNormal) as Normal by Xval

| rename COMMENT as "And we hide the parts of the labels that we don't need, so it's pretty."
| eval Xval=case(len(Xval)=5,Xval,len(Xval)=6," ",true(),substr(Xval,6,7))
| rename Xval as "Monthly Results"

[DalJeanis]

For the output display, you'll need to select "stacked bar chart". You'll also need to (unfortunately) keep the output results down to roughly 6-8 months or the values of the date on the X axis will start getting smooshed.

[matansocher]

It is very close to what I need. Thanks!

[niketn]

Updated final query below, give following a try and see if it gets close:

<Your Base Search>
| eval timeField = year+"."+ month
| eval State=if(match(dv_state,"Open"),"Open","Closed")
| eval key = timeField." - ".State 
| chart count(eval(priority=="normal")) as normal count(eval(priority=="high")) as high over key

For the chart visualization you can display values like count volume on one axis(in this case count of priority is on y-axis). You can plot series on x-axis which in this case is the key (combination of timeField and State as per your screenshot).

---

Reverse timeField to YY-MM it should sort (Numeric fields are being compared as string to YY-MM should be ascending order like 16-01, 16-02, 17-01, 17-02.

For second piece you can add a State field as Open or Closed (You are already filtering only Open Or Closed events in your base search so whatever is not Open is Closed.

<Your Base Search>
| eval timeField = year+"."+ month
| eval State=if(match(dv_state,"Open"),"Open","Closed")
| eval key = priority." - ".State
| chart count over "timeField" by key

[matansocher]

Thank you for your answer. Your suggested solution gives me some kind of a solution, but I need it as the excel example. The main idea of that report is to show open vs close bars to show the differences between the total number of the 2.

[matansocher]

Thanks again for your help.
I replaced month and year and it solved that problem.
Your suggestion to the main problem is good, but I still cannot show my result like the excel result. I am not sure if it is even possible.
Do you think it is possible to show the result for every month to bars(open and closed) and for each bar to divide it for 2(high and normal).

Thank you again!