Splunk Enterprise

How to chart year without the comma using scatterplot?

zerosones
Engager

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

0 Karma

somesoni2
Revered Legend

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

or

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")

OR

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

zerosones
Engager

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

0 Karma