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
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
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
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
results are returned in the following format:
Category, Order Year, Profit
Furniture, 2011-01-05, 5450
And all the values are bunched together on x=0.
Thank you