Need help in creating a stacked bar chart graph showing completion percentage

[maverick27]

Hello Splunk experts, 

I'm pretty new to splunk and I would like your help in forming a query for the following requirement.

I would like to create a bar chart for each OEM (total of 5 seperate bar charts widgets since we have 5 OEMs) based on the completion progress of NCAPTest. So, these events will be pushed to Splunk every Monday. The x-axis should show the timestamp(_time) in the following format(YYYY-MM-DD) and the y axis should show stacked bar graph where bottom portion of the bar should show completed count(NCAPTest=Yes) along with the completion percentage and the top portion should show the remaining count(NCAPTest=No).

This is how the data looks like:

6 Nov, 2023 events:

OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	No
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	No
Kia	Seltos	SUV	No
Kia	Forte	Sedan	No
Kia	Rio	Hatchback	No
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No

13 Nov 2023 events:

Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	No
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No

20 Nov 2023 events:

Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	Yes
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	Yes
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	Yes
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	Yes
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	Yes
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	Yes

Any help is greatly appreciated. 

[maverick27]

This is a follow up question though.. I was wondering if we can display the NCAPTest='Yes' or 'No' count along with percentage? And any way to add a percentage symbol(%) on the y axis which has intervals of 50?

[ITWhisperer · Accepted solution]

| makeresults
| eval _time=strptime("6 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	No
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	No
Kia	Seltos	SUV	No
Kia	Forte	Sedan	No
Kia	Rio	Hatchback	No
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"
| append
    [| makeresults
    | eval _time=strptime("13 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	No
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	No
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	No
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	No
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	No
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	No"]
| append
    [| makeresults
    | eval _time=strptime("20 Nov 2023","%d %b %Y")
| eval _raw="OEM	Model	Type	NCAPTest
Honda	Civic	Sedan	Yes
Honda	CR-V	SUV	Yes
Honda	Fit	Hatchback	Yes
VW	Jetta	Sedan	Yes
VW	Tiguan	SUV	Yes
VW	Golf	Hatchback	Yes
Tata	Harrier	SUV	Yes
Tata	Tiago	Hatchback	Yes
Tata	Altroz	Hatchback	Yes
Kia	Seltos	SUV	Yes
Kia	Forte	Sedan	Yes
Kia	Rio	Hatchback	Yes
Hyundai	Elantra	Sedan	Yes
Hyundai	Kona	SUV	Yes
Hyundai	i20	Hatchback	Yes"]
| multikv forceheader=1
| table _time OEM	Model	Type	NCAPTest
``` The lines above create sample events in line with your example ```
``` Count total by time and OEM ```
| eventstats count as total by _time OEM
``` Count by time OEM total and test result ```
| stats count by _time OEM total NCAPTest
``` Determine percentages ```
| eval count=round(100*count/total,2)
``` Separate yes and no percentages ```
| eval {NCAPTest}=count
``` Gather no and yes percentages by time and OEM ```
| stats values(No) as No values(Yes) as Yes by _time OEM
``` Fill nulls with zero percent ```
| fillnull value=0.00 Yes No

For visualisation, use column chart with trellis

[maverick27]

You are a genius. Thank you so much @ITWhisperer . Much appreciated! 🙂