chart by date ignoring year

[jyates76]

Im using the search below and basically want a chart showing last 12 dates going oldest to newest from left to right by date. 

| inputlookup running_data.csv
| eval EP=strptime(Date, "%m/%d/%Y")
| eval Date=strftime(EP, "%m/%d/%Y")
| chart sum(sats) over team by Date useother=false limit=12
| fillnull value=0

The search was working fine up until January and year change, now the search only shows the last date in December and is missing the newest  01/02/2024 date.  If I change the limit to be large enough to include all date entries in the csv file, I discovered the below:

Its putting the 01/02/2024 date before the oldest date in the csv, instead of putting that 01/02/2024 column after the 12/18/2023 date column.  So its like its ignoring the year and going by month chronologically. Done quite a bit of searching on this to no avail, and seems like this should be an easy thing to do... Im not opposed to not using "chart" if someone has a better way.  Ideally the search returns the last 12 dates from oldest to newest in the columns and then the team name and numbers sats on that date in the rows.  Thansk for any suggestions!

[PickleRick]

Wait a second. What's the point of doing strptime/strftime over the same value with the same format?

[dtburrows3]

My assumption is that we are stripping off HH:MM:SS from the original value of Date, but we still want the final results to be in a formatted %m/%d/%Y.

Hard to say for sure without seeing the original dataset.

[PickleRick]

For that it would be easier to just cut the date after space. Also working with string-formatted timestamps is just asking for trouble.

[dtburrows3]

I think doing something like this should work.

| inputlookup running_data.csv
    | eval
        EP=strptime(Date, "%m/%d/%Y")
    | chart
        sum(sats) as sats
            over EP
            by team
    | sort 0 +EP
    | eval
        Date=strftime(EP, "%m/%d/%Y")
    | fields - EP
    | transpose 25 header_field=Date
    | rename
        column as team

This will first sort the dates while they are in epoch time and then we convert to human readable timestamps. Then, a transpose is used to retain the order of ascending time from left to right in the header.
Screenshot of local example:

[jyates76]

Thanks, this actually is close with some tweaking but I still cant get around the fact that after the transpose, I want it show the latest 12... Transpose 25 for example will get me the first 25 dates left to right and I want the last 12 right to left if that makes sense?    I could do Transpose with no integer to show everything, but then that would be an extremely wide table as this data grows over time on a weekly basis we get a new date, and on those dates we are trying to show number of sats per team for all teams on that date.

[dtburrows3 · Accepted solution]

I think transforming the data in a normal Splunk timechart format then doing a head 12 and then transposing should do what you are asking.

| inputlookup running_data.csv
    | eval
        _time=strptime(Date, "%m/%d/%Y")
    | sort 0 -_time
    | timechart span=1d
        sum(sats) as sats
            by team
    | head 12
    | eval
        Date=strftime(_time, "%m/%d/%Y")
    | fields - _*
    | transpose 12 header_field=Date
    | rename
        column as team

Example output:

[jyates76]

close enough.. I got it down to showing it dates and what I needed in correct order but from right to left using the below

| inputlookup running_data.csv
| eval _time=strptime(Date, "%m/%d/%Y")
| sort 0 -_time
| timechart span=1d sum(sats) as sats by team useother=false limit=0
| fillnull value=0
| tail 12
| eval Date=strftime(_time, "%m/%d/%Y")
| fields - _*
| transpose 12 header_field=Date
| rename column as team

[madhav_dholakia]

Hi, is it possible to change the date format to YYYY-MM-DD and then something like this? 

| chart sum(sats) over team by Date useother=false
| sort 13 Date

Thank you.