topic Re: How to create a column chart with line chart in Splunk Search

How to create a column chart with line chart

jenniferhao — Wed, 30 Sep 2020 01:52:05 GMT

I need to make a chat similar to the following picture base on the data below.
The column chart should show 2 columns for count_num1 and count_num2 groups by reg.
The line on the chart is the percentage which is count_num1/count_num2%.
The X-Axis is _time, Y_Axis(left) is count_num1 and count_num2.Y_Axis(right) is percentage.

Do you know have any idea to do this? Thanks.

_time reg count_num1 count_num2 percentage
2019-04-06 A 70895 138861322 0.05
2019-04-06 B 84193 133480859 0.06
2019-04-06 C 63006 89228628 0.07
2019-04-06 D 67866 69157557 0.10
2019-04-06 E 30690 21232372 0.14
2019-04-06 F 192345 103945927 0.19
2019-04-13 A 69890 138251510 0.05
2019-04-13 B 63187 123896970 0.05
2019-04-13 C 63685 91410203 0.07
2019-04-13 D 78028 69272610 0.11
2019-04-13 E 30406 20989378 0.14
2019-04-13 F 198410 104328969 0.19
2019-04-20 A 70214 137347777 0.05
2019-04-20 B 81601 118578993 0.07
2019-04-20 C 63630 91699136 0.07
2019-04-20 D 81757 69488471 0.12
2019-04-20 E 30529 21195290 0.14
2019-04-20 F 199741 104744328 0.19

Re: How to create a column chart with line chart

Sukisen1981 — Thu, 22 Aug 2019 15:26:16 GMT

hi please go to your chart?format viz> overlay and select your % as an overlay field, the rest of the columns choose stacked under general settings

Re: How to create a column chart with line chart

niketn — Wed, 30 Sep 2020 01:52:18 GMT

@jenniferhao what happens to count_1 and count_2. Which one of them are you using as stacks for each reg? Seems like count_1 is some count and count_2 is the total which is used to calculate percent. Which implies you need to show count_1 as stacks for each reg over time. Is this correct?

Also your percent in the table is per reg. However, percent in your chart is per day. What is the aggregation to convert percent into daily percent? Is it sum() across all reg or avg() across all reg?

Re: How to create a column chart with line chart

niketn — Thu, 22 Aug 2019 18:31:26 GMT

@jenniferhao following is a run anywhere example based on the sample data and screenshot provided. It makes following assumptions

1) count_1 is the field for stacks per day for each reg
2) Percentage is average of percentage of all regs per day

Following is the simpleXML code for the run anywhere example where SPL pipes till | fields _time reg count_num1 count_num2 percentage generates the data as per your example.

<dashboard>
  <label>Stacked Time chart with percent</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults 
| eval data="2019-04-06 A 70895 138861322 0.05;
2019-04-06 B 84193 133480859 0.06;
2019-04-06 C 63006 89228628 0.07;
2019-04-06 D 67866 69157557 0.10;
2019-04-06 E 30690 21232372 0.14;
2019-04-06 F 192345 103945927 0.19;
2019-04-13 A 69890 138251510 0.05;
2019-04-13 B 63187 123896970 0.05;
2019-04-13 C 63685 91410203 0.07;
2019-04-13 D 78028 69272610 0.11;
2019-04-13 E 30406 20989378 0.14;
2019-04-13 F 198410 104328969 0.19;
2019-04-20 A 70214 137347777 0.05;
2019-04-20 B 81601 118578993 0.07;
2019-04-20 C 63630 91699136 0.07;
2019-04-20 D 81757 69488471 0.12;
2019-04-20 E 30529 21195290 0.14;
2019-04-20 F 199741 104744328 0.19" 
| makemv data delim=";" 
| mvexpand data 
| rex field=data "(\s|\n?)(?<data>.*)" 
| makemv data delim=" " 
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d"),
    reg=mvindex(data,1),
    count_num1=mvindex(data,2),
    count_num2=mvindex(data,3),
    percentage=mvindex(data,4) 
| fields _time reg count_num1 count_num2 percentage
| bin _time span=1d
| eventstats avg(percentage) as percentage by _time
| eval Time_Perc=_time."|".percentage
| chart latest(count_num1) by Time_Perc reg
| eval _time=mvindex(split(Time_Perc,"|"),0), Percentage=mvindex(split(Time_Perc,"|"),1)
| fields - Time_Perc
| table _time * Percentage
| timechart latest(*) as * cont=f</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">1</option>
        <option name="charting.axisY2.scale">log</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.overlayFields">Percentage</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">bottom</option>
        <option name="charting.lineWidth">2</option>
        <option name="height">434</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>

Re: How to create a column chart with line chart

jenniferhao — Thu, 22 Aug 2019 20:16:52 GMT

To provide further feedback to my original question.
I need to create a Time chart type diagram
i have various KPI's for 1 type of metric i want stacked. and then another type metric stacked beside it.
but for 2 fields side by side are not stacked.

similar to the image below.