I have a week_number field in my data. I want to display each week_number with the date of 1st day in that week.
Ex-
week number Date
1 01/01/2019
2 08/01/2019
Can you please provide with the code for this
Try something like this (run anywhere sample, replace everything before eval with your base search)
| gentimes start=-10 | streamstats count as weekno | table weekno | eval firstOfWeek=strftime(relative_time(now(),tostring(weekno-tonumber(strftime(now(),"%V")))."w@w"),"%F %a")
This is treating Sunday as the first day of week. IF you want Monday, change w@w
with w@w1
.
I applied the same code and below is the output. The code is not working for dates of previous year. I used this line in my code.
eval firstOfWeek=strftime(relative_time(now(),tostring(weekno-tonumber(strftime(now(),"%V")))."w@w"),"%F ")
Date weeknum firstOfWeek
06/03/2019 10 2019-03-04
06/03/2019 10 2019-03-04
14/03/2019 11 2019-03-11
23/03/2019 12 2019-03-18
24/03/2019 12 2019-03-18
01/10/2018 40
03/10/2018 40
03/10/2018 40
07/10/2018 40
08/10/2018 41
09/10/2018 41
10/10/2018 41
11/10/2018 41
11/10/2018 41
Can you specify the solution?
Try something like this:
BASE SEARCH GOES HERE...
| eval modifier=case(date_wday == "sunday", 0, date_wday == "monday", 1, date_wday == "tuesday", 2, date_wday == "wednesday", 3, date_wday == "thursday", 4, date_wday == "friday", 5, date_wday == "saturday", 6, 1=1, 0)
| eval first_wday_date=_time - (modifier * 86400)
| eval first_wday_date=strftime(first_wday_date, "%m/%d/%y")
| table date_wday _time modifier first_wday_date
This is assuming Sunday is the first day of your week. I wasn't sure if you wanted it to be Monday. If so, just mess with the modifiers (the amount of days to subtract from the current weekday, to get you to the beginning day of the week. This also assumes, you could go back into the previous month if the beginning of the week was not in the current month.
In the image below, the table has the current day of the week, the current date, the modifier value, and the date of the first day of the week.
@nikita012 asks how to translate week_number into a date (first day of that week). You're using _time. Not sure how that is a solution to the question?
Assuming you want to get the date of the monday of that week number, I would say this should work:
| eval date="1 ".week_number." 2019"
| eval date=strftime(strptime(date,"%w %V %Y"),"%d/%m/%Y")
But for some reason it is not able to parse that string using day of week, week number and year into a timestamp...