How to sort date fields chronologically in a stacked bar chart and group dates by week numbers?

[timgirgis]

I have a CSV import that has a date field in the format dd/mm/yyyy that I want to be able to chart chronologically on the x-axis in a graph in Splunk. However, when the field is sorted, it sorts the dates based on the dd and not the actual date e.g. 01/02/2016, 01/05/2016 instead of 01/02/2016, 03/02/2016 etc. How do I tell Splunk to recognize the field as a date and sort chronologically (there are no time stamps)?

As a second question, how do I group these dates up into weeks on a graph? I want to show all the dates between Monday and Sunday on the first week into Week 1, then those dates in Week 2 etc.

The end goal is to plot out a simple stacked bar chart where "Delivery Start _ Triage Date" is the date (grouped by week and plotted chronologically) along the x-axis, and "Title" is counting the number of projects along the y-axis. "Pipeline" is what I want to break the stacks into (it shows the office where these projects are taking place).

index="pipedrive_modified" | sort by "Delivery Start _ Triage Date" | chart count("Title") over "Delivery Start _ Triage Date" by "Pipeline"

Obviously I want this particular search to group dates by week and then plot them chronologically, but I would also love to know the logic behind / how to get Splunk to recognize fields as a date range and then sort them chronologically (without grouping them into weeks - as per my first query).

Thanks

[renjith_nair]

You can convert a string to date by using strptime(field,"format")

For sorting , try

index="pipedrive_modified" | eval my_date=strptime(datefield,"%d/%m/%Y")|sort my_date

For the final search try

index="pipedrive_modified" | eval my_date=strptime(datefield,"%d/%m/%Y")|eval week_no=strftime(my_date,"%U")|chart count(Title) over week_no by Pipeline

[timgirgis]

Hi Renjith,

We couldnt get the query to return the results we wanted, it is still sorting by the day field and not the day/month combination. Any idea why?

[somesoni2]

Can you provide the query that you tried (and didn't work)?

[renjith_nair]

That's strange. Are you sure your date column is string and not date and is in dd/mm/yyyy format?

I have tried with a dummy data and is working as below.

|stats count|eval dt="01/02/2016,02/02/2016,08/02/2016,01/08/2016,02/12/2016,10/01/2016" |eval splitted=split(dt,",")
|mvexpand splitted|fields splitted|eval converted=strptime(splitted,"%d/%m/%Y")|sort converted

here if you sort by "dt" and "sorted" , you can see the difference in the order.