topic Re: How to chart year without the comma using scatterplot? in Splunk Enterprise

How to chart year without the comma using scatterplot?

zerosones — Sun, 12 Feb 2017 22:18:29 GMT

Hi Splunk community,

I have the following search to chart the profit of each product category by year. "Order Date" values are in format "02/14/2013".

index="sales_orders" 
| eval Profit = round(Profit, 0) 
| eval order_year = strftime(strptime('Order Date',"%m/%d/%Y"),"%Y")
| stats sum(Profit) as Profit by order_year, Category
| table Category,  order_year, Profit

When executed, the order_year column on Statistics tab returns values as the following without a comma separator:

2013
2014

But when it's charted on the Visualization tab as scatterplot, the x-axis labels are showing "2,013", "2,014" instead with a comma separator.

I've tried turning off the comma separator in the order_year column and that didn't seem to affect the visualization.

I then thought I try to create a string field for the order year and graph by the string version of the year instead:

index="sales_orders" 
| eval Profit = round(Profit, 0) 
| eval order_year = strftime(strptime('Order Date',"%m/%d/%Y"),"%Y")
| stats sum(Profit) as Profit by order_year, Category
| eval "Order Year" = tostring(order_year)
| table Category,  "Order Year", Profit

Still, the scatter plot x-axis label is showing "2,012" "2,013", etc even though the "Order Year" values on the Statistics tab shows "2012" "2013".

Can anyone guide me on how to get rid of the comma separator in x-axis when charting?

Thanks in advance

Re: How to chart year without the comma using scatterplot?

somesoni2 — Mon, 13 Feb 2017 04:31:44 GMT

Give these a try

index="sales_orders" 
 | eval Profit = round(Profit, 0) 
 | eval order_year = strftime(strptime('Order Date',"%m/%d/%Y"),"Y%Y")
 | stats sum(Profit) as Profit by order_year, Category
 | rename order_year as "Order Year"
 | table Category,  "Order Year", Profit

index="sales_orders" 
| eval Profit = round(Profit, 0) 
| eval _time= strptime('Order Date',"%m/%d/%Y")
| stats sum(Profit) as Profit by _time Category
| fieldformat _time=strftime(_time,"%Y")

index="sales_orders" 
| eval Profit = round(Profit, 0) 
| eval _time= strptime('Order Date',"%m/%d/%Y")
| stats sum(Profit) as Profit by _time Category

Re: How to chart year without the comma using scatterplot?

zerosones — Wed, 15 Feb 2017 00:59:41 GMT

Hi somesoni2,

Thank you for the suggestions. I tried all there and here are the results. Unfortunately, the x-axis is still not showing the year or date correctly.

== Set 1==
- on Statistics tab

results are returned in the following format:
Category, Order Year, Profit
Furniture, Y2011, 5450

on Visualization tab When visualized results using Scatter Chart, the X axis values are showing as 0 10 20 30 40 50 ... 90 instead of Y2011 Y2012... etc

And all the values are bunched together on x=0. Not sure why that is happening.

== Set 2==
I added following line to the search to format data for the scatter plot

| table Category, _time, Profit

on Statistics tab
the results are returned in the following format:
Category,_time, Profit
Furniture, 2011, 5450
on Visualization tab
When visualized results using Scatter Chart, the X axis values are showing as
2,010 2,011 2,012 2,013

Looks like the scatterplot is still interpreting the x axis values as numeric.

== Set 3==
I added following line to the search to format data for the scatter plot

| table Category _time Profit

on Statistics tab

results are returned in the following format:
Category, Order Year, Profit
Furniture, 2011-01-05, 5450

on Visualization tab When visualized results using Scatter Chart, the X axis values are showing as 0 10 20 30 40 50 ... 90 instead of 2011-01-05 2011-01-10 etc..

And all the values are bunched together on x=0.

Thank you