Splunk Search

how to order timeline in correct week order?

New Member

Hi
my data is .csv file manually uploaded to Splunk cloud. there are columns for year, month, week numbers.

I write below search to generate line chart to see weekly trend.

index="price"
Series="abc"
Form="1"
Product_Name="white"
|eval Year.Week = Year."-w".Week."-month".Month
|stats max(Price) AS max
median(Price) AS median
min(Price) AS min

median(sp) AS sp.com by Year.Week

But the result is presented in this order:
2017-w1-month1
2017-w10-month3
2017-w11-month3
2017-w2-month1

I want it in correct order:
2017-w1-month1
2017-w2-month1
...
2017-w9-month3
2017-w10-month3
...

Please help. I'm new in Splunk

0 Karma
1 Solution

Super Champion

try doing it like this:

index="price"
Series="abc"
Form="1"
Product_Name="white"
|eval YearWeek = Year."-w".Week."-month".Month
|stats max(Price) AS max
median(Price) AS median
min(Price) AS min
median(sp) AS sp.com by YearWeek Year Week
|sort Year Week|fields - Year - Week

it's treating your YearWeek like a text field since it's concatenated together. you'll need to sort them as numbers, not strings.

View solution in original post

0 Karma

Champion

You should just pad the Week and Month values with 0's.

Try this change:

|eval YearWeek = Year."-w" + tostring(if(Week < 10, "0"+Week, Week)) + "-month" + tostring(if(Month < 10, "0"+Month, Month))

New Member

This also works. Thank you!

0 Karma

Super Champion

try doing it like this:

index="price"
Series="abc"
Form="1"
Product_Name="white"
|eval YearWeek = Year."-w".Week."-month".Month
|stats max(Price) AS max
median(Price) AS median
min(Price) AS min
median(sp) AS sp.com by YearWeek Year Week
|sort Year Week|fields - Year - Week

it's treating your YearWeek like a text field since it's concatenated together. you'll need to sort them as numbers, not strings.

View solution in original post

0 Karma

New Member

Thank you! this works!

0 Karma

SplunkTrust
SplunkTrust

I would also format the week number as rjthibod suggested, since it will present the YearWeek in a more coherent fashion.

0 Karma