Want latest weeks data in every month in a monthly chart

[prathikpisplunk]

Below is my requirement.

I have weekly data for 24 weeks ( 6 months) , I want to get data of last month in every week and show in a monthly chart which would be for 6 months.
For ex:
week1
week2 ------------------- > week4 data will be shown in month1 in monthly chart
week3

week4

week1
week2 ------------------- > week4 data will be shown in month2 in monthly chart
week3
week4

Like above I need data for 6 months from 24 weeks chart.

I have existing query for 24 weeks chart.

index ="83299-np" earliest=-24w@w6 latest=now sourcetype = "psirt_details" matchConfidence!="Not Vulnerable"

[
| tstats max(_time) as maxTime WHERE index ="83299-np" earliest=-24w@w7 sourcetype="psirt_details" by source _time span=1w

| sort -maxTime

| stats first(source) as source by _time

| fields source]

| fields psirtColdId,matchConfidence,source

| eval dayWeek = strftime(relative_time(_time,"@w6"),"%Y-%m-%d")

| eval workField = psirtColdId.":".dayWeek

| dedup workField,matchConfidence

| replace "Potentially Vulnerable" with "Potentially" in matchConfidence

| stats list(matchConfidence) as matchConfidence by workField

| eval statusOuput=if(matchConfidence LIKE "Potentially" AND !(matchConfidence LIKE "Vulnerable"),"Potentially Vulnerable","Vulnerable")

| eval id=mvindex(split(workField,":"),0)

| eval dayWeek=mvindex(split(workField,":"),1)

| chart dc(id) over dayWeek by statusOuput

how do I modify this query for the expected result?

[gokadroid]

How about you put in a week field which will have values like Week 1, Week 2, Week 3 and Week 4 and monthYear field which will have values like 05-2016, 06-2016 etc. in your query for every event and then finally you use chart command to do the stats bit something like this below:

your query to return the data for all 24 weeks
| eval week="Week ".tostring(tonumber(strftime(_time, "%U"))%4+1)
| eval monthYear=strftime(_time, "%m")."-".strftime(_time, "%Y")
:
:
|chart dc(id) over monthYear by week

[sundareshr]

@prathikpisplunk, I'm not sure I understand which fields value for last month you need to capture, so here is the logic to get the value for last week of month for everymonth for field of your choice. You should be able to insert this in your query at the appropriate place(s) to get your desired output

| eval wknum=strftime(_time, "%U")
| eval wknum_mon_start=strftime(relative_time(_time, "@mon")
| eval wknum_mon=wknum-wknum_mon_start
| eval mon=strftime(_time, "%b")
| eventstats max(wknum) as max_mon by mon
| eval fieldvalue=if(max_mon=wknum, fieldvalue, null()))