topic Re: multiple BY clause in chart/stat column in Splunk Search

How to write search with multiple BY clause in chart/stat column?

GOSWAMIGAURAV — Wed, 25 May 2022 11:16:00 GMT

Hello,

I have a query which returns Planned_Sprint, Total Hours,Actual Hours,Team,Type. Now i want a stacked bar and line chart , in which the stacked bars are split by Planned_Sprint,Type and the line is only Split by Planned_Sprint.

The X-axis as Planned_Sprint, the Y as (Total Hours)stacked bar and (Actual Hours)line.

Planned_Sprint	Total Hours	Actual Hours	Type	Team
Sp_1	10	20	A	WWW
Sp_1	15	10	B	DDD
Sp_1	5	10	B	RRR
Sp_2	10	15	A	WWW
Sp_2	20	5	A	DDD
Sp_2	10	10	B	TTT
Sp_2	5	8	C	RRR
Sp_3	20	20	B	TTT

Here is my code for the chart:

| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type

But in this even the AC gets split by Type. I do not want this, i want something like this:

| chart sum(Total Hours) AS PLANNED_Hours by Planned_Sprint,Type, sum(Actual Hours) as AC by Planned_Sprint

Any suggestion how this can be achieved?

Re: multiple BY clause in chart/stat column

Software-Simian — Wed, 20 Apr 2022 09:28:09 GMT

Hi,

the easiest way would be an append...i am sure that there are multiple solutions out there to get this in one Go....but why bother if it can be achieved by a simple "append"

Regards

Re: multiple BY clause in chart/stat column

isoutamo — Wed, 20 Apr 2022 09:36:14 GMT

you should do it something like

<your base search> | stats sum('Total Hours') AS PLANNED_Hours sum('Actual Hours') as AC by Planned_Sprint

Then select visualisation as bar chart and then there is an Overlay option where you should select AC.

Unfortunately I'm not getting what you want to do with Type or is it even relevant?

r. Ismo

Re: How to write search with multiple BY clause in chart/stat column?

ITWhisperer — Wed, 25 May 2022 13:01:34 GMT

Re: multiple BY clause in chart/stat column

Software-Simian — Wed, 20 Apr 2022 09:44:01 GMT

Hi Iso,

i just assumed that he wants to do:

Total Hours in case Type is A

Actual Hours in Case Type is B

basically he tried using by as a filter...i think

Re: multiple BY clause in chart/stat column

GOSWAMIGAURAV — Wed, 20 Apr 2022 09:56:54 GMT

This will not work..The total hours are split by both Planned_Sprint and "Type" for each team.So the x axis becomes the Planned_Sprint and the y axis has two things: the sum of "Total Hours" split across various "Types" as stacked bars and the actual Hours Line graph should be just sum(Actual Hours ) split across Planned_Sprint

Re: multiple BY clause in chart/stat column

GOSWAMIGAURAV — Wed, 20 Apr 2022 10:28:49 GMT

Hello,

I have edited my question with the sample table and expected chart

Re: multiple BY clause in chart/stat column

GOSWAMIGAURAV — Wed, 25 May 2022 11:31:20 GMT

@ITWhisperer I just figured, this works only if all or at least 2 "Type" are present. For the final row in my table above,where Planned Sprint = Spr_3,the Actual hours(AC) line does not come up as Spr_3 has only one Type unlike Spr_1 and Spr_2. Although there is data in Actual hours(AC) column.Any help here?

Re: multiple BY clause in chart/stat column

GOSWAMIGAURAV — Wed, 25 May 2022 12:58:38 GMT

I have figured it out, the null values were causing problems. |Fornull value=0 solves it