I have a set of data where the date is stored as Year/Week of Year.
For example: this week would be 14/43 while the first week of January first would be 14/1.
I would like to convert this into something easily readable, likely Month/Day/Year.
As Week of Year doesn't seem to be a standard time/date variable, I'm having trouble figuring out a solution using time conversion methods. Any pointers would be welcome.
Is this based on Fiscal calendar? what i am going to suggest might be the hardest way. Here's what i think should do.
Going by your example, 14/43
i would extract it rex "^.*\/(\d+)\s". Now i should have Week field = 43
A giant if loop , Eval Month = if((Week<=5),"January",if(Week>5 AND Week <=10),February..........)
Now you have both Week and Month fields. Concatenate Year and Month.
Try something like this (runanywhere sample)
|gentimes start=-1 | eval temp="14/43" | table temp | eval newDate=relative_time(strptime(mvindex(split(temp,"/"),0)."/01/01","%y/%m/%d") + tonumber(mvindex(split(temp,"/"),1))*86400*7 - 86400,"@w") | convert ctime(newDate)
You can use the expression into a macro (eval newDate).
Assuming your week starts with a Monday, here's a pretty way of doing this:
| stats count | eval in = "2014/43" | eval out = strptime(in." 1", "%Y/%U %w") | eval formatted = strftime(out, "%+")
That yields Monday, October 27th 00:00:00 for me. Change the 1 in the
strptime() to 0 if your weeks start with Sunday.
Strip out the month and go from there? The week number should be sufficient.
Alternatively, add the month to the strptime.