Let's say I have a lookup table and I have it formatted and "searched" down to:
_time | Cat_1 | Cat_2 | Cat_3 | Cat_4 | totalCount
2018-04. 1 1 0 5 7
2018-05 2 3 1 0 6
2018-06 3 1 0 0 4
using:
| inputlookup File.csv
| eval _time=strptime(Date, "%m/%d/%Y")
| where _time>relative_time(now(), "-5mon@m")
| timechart span=1mon count by "other_field"
| addtotals fieldname=totalCount Cat_1 Cat_2 Cat_3 Cat_4
What I want is a stacked column chart over time by month. Essentially a way to count the TOTAL number of ALL events from month to month, displaying it as an overlay. I have gotten it working with non-lookups but in this case, it requires a lookup.
I can modify the lookup as needed. I'd like to be able to show the categories per month and then an overlaid line showing a positive upwards trend with the totals. I tried bucket but am not getting the results I desire.
@drico618
Can you please try below XML?
<dashboard>
<label>dummy1</label>
<row>
<panel>
<chart>
<search>
<query>|makeresults | eval _raw="
_time Cat_1 Cat_2 Cat_3 Cat_4 totalCount
2018-04 1 1 0 5 7
2018-05 2 3 1 0 6
2018-06 3 1 0 0 4
" | multikv | table time Cat_1 Cat_2 Cat_3 Cat_4 totalCount</query>
<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.scale">linear</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</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">totalCount</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.placement">right</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
</chart>
</panel>
</row>
</dashboard>
My concern is that the below query isn't looking at the lookup table which is in CSV format. It's standard comma delimited:
|makeresults | eval _raw=
_time Cat_1 Cat_2 Cat_3 Cat_4 totalCount
2018-04 1 1 0 5 7
2018-05 2 3 1 0 6
2018-06 3 1 0 0 4
" | multikv | table time Cat_1 Cat_2 Cat_3 Cat_4 totalCount