topic Re: How to get the calender week from the date? in Dashboards & Visualizations

How to get the calender week from the date?

anooshac — Wed, 13 Sep 2023 10:22:48 GMT

Hi Everyone,

I want to plot a chart according to the calendar week. I plotted a timechart like this,

|timechart span=7d distinct_count(Task_num) as Tasks by STATUS

But this doesn't give the exact calendar weeks. Also i am keeping this charts data to last 3 months.

Anyone have idea how to plot a bar chart based on calendar week? instead of date i want to see the data for current calendar weeks of last 3 months.

I got from the splunk community on how to get the Calendar week. But i am not to plot a graph out of it.

| eval weeknum=strftime(strptime(yourdatefield,"%d-%m-%Y"),"%V")

Re: How to get the calender week from the date?

richgalloway — Wed, 13 Sep 2023 12:07:44 GMT

Untested, but try the chart command.

| eval weeknum=strftime(strptime(yourdatefield,"%d-%m-%Y"),"%V") | chart dc(Task_num) as Tasks over weeknum by STATUS

Re: How to get the calender week from the date?

anooshac — Thu, 14 Sep 2023 03:56:44 GMT

Mt date field is _time. When i use this query i'm getting as no results found.

| eval weeknum=strftime(strptime(_time,"%d-%m-%Y"),"%V") | chart dc(Task_num) as Tasks over weeknum by STATUS

Re: How to get the calender week from the date?

isoutamo — Thu, 14 Sep 2023 06:56:44 GMT

maybe you could try to get data by adding this into your base search ?

earliest=-3mon@w+d latest=@w+d

Re: How to get the calender week from the date?

richgalloway — Thu, 14 Sep 2023 12:13:21 GMT

_time is already in epoch form so it does not need to be (and cannot be) converted using strptime.

| eval weeknum=strftime(_time, "%V") | chart dc(Task_num) as Tasks over weeknum by STATUS

Re: How to get the calender week from the date?

anooshac — Fri, 15 Sep 2023 11:59:15 GMT

Thank you so much.. I totally forgot that it is already in epoch format.

Re: How to get the calender week from the date?

anooshac — Fri, 23 Feb 2024 08:44:26 GMT

Hi @richgalloway ,

Is it possible to sort this according to the year? My search query i have set for last 3 months data, it is taking last years data. But in chart the calendar week 48,49 etc are coming at the end. But they should be in the beginning. I am trying out some solutions but not able to do it properly. Do you know any suggestion for this?

Re: How to get the calender week from the date?

richgalloway — Fri, 23 Feb 2024 14:09:55 GMT

Numbers always sort in numerical order. To change the order, change the numbers by adding the year to them.

| eval weeknum=strftime(_time, "%y-%V") | chart dc(Task_num) as Tasks over weeknum by STATUS

Re: How to get the calender week from the date?

anooshac — Tue, 27 Feb 2024 05:51:24 GMT

Hi @richgalloway , i added the year and it is working fine now. I want to only show the calendar week in the chart, any solution for this?

What i understand is that we cannot change the labels.

Re: How to get the calender week from the date?

richgalloway — Tue, 27 Feb 2024 13:20:06 GMT

It is true one cannot change the labels. That means we have to choose between having week numbers in numerical rather than calendar order or having year-week numbers in calendar order.