stacked bar chart with my data- is it possible?

[mjm295]

Hi

I have a search which gives data similar to:

Name,   X1,  X2,  Y1,   Y2,  Z1,  Z3
name1,  A,      ,  A ,   ,   A,
name2,  A,      ,    ,   ,   A,
name3,  A,      ,  A ,   ,    ,   A
name4,    ,    A,  A ,   ,   A,
name5,  A,      ,  A ,   ,    ,
name6,  A,      ,  A ,   ,   A,
name7,  A,      ,  A ,   ,    ,   A      

I am trying to create a stacked bar chart with 3 columns X,Y and Z
With:
column X made up from the total of X1 plus X2 (eg 6+1)
column Y made up from the total of Y1 plus Y2 (eg 6+0)
column Z made up from the total of Z1 plus Z2 (eg 4+2)

The end of my search currently has:
| table name X1 X2 Y1 Y2 Z1 Z2

Thanks
Mark

[DMohn]

This is for sure not the prettiest solution, but it still serves the case, and it might lead to a suitable result 🙂

I have created an input using your fields in a CSV, so the fields names in the data are as per your example.

<your base search> 
| stats count(X*) as SumX*, count(Y*) as SumY*, count(Z*) as SumZ* 
| transpose 10 column_name=col 
| eval colName=case(match(col,"X"),"X",match(col,"Y"),"Y",match(col,"Z"),"Z") 
| chart sum("row 1") over colName by col

You need to adjust the value for transpose 10 to the maximum number of fields (X*,Y*,Z*) your data contains.

[mjm295]

Thanks, managed to make that work.